Showing posts with label conversion. Show all posts
Showing posts with label conversion. Show all posts

Thursday, March 22, 2012

Date Parameters show as Strings when using Analysis Services as a data source

We have been a Crystal shop for ages; we are currently doing a proof-of-concept for a conversion to MS Reporting Services. As such, we are developing some Analysis Services 2005 cubes to drive some new SSRS reports, which our users will access through Report Manager. Unfortunately, we are all MDX noobs here, so we are making heavy use of the Wizards until we can come up to speed.

The problem we are running into is when we develop a report with Date Parameters. When we deploy this report, the date parameter box is a dropdown box instead of a date picker. I've seen a couple of other posts on this topic, but when I try to apply the fixes mentioned in them, I throw errors.

I have two quick questions:

    Why does this happen? Is it a limitation in the MDX language, in SSAS, or SSRS? Are there any planned fixes? Can someone please show me how to fix this on my actual query string for one of our basic reports? I've highlighted the date parameters.

Code Snippet

SELECT NON EMPTY { [Measures].[Lead] } ON COLUMNS, NON EMPTY { ([Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.LeadSourceTypeLeadSourceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.StoreStoreID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@.FromLeadCreationDateCalendarDate, CONSTRAINED) : STRTOMEMBER(@.ToLeadCreationDateCalendarDate, CONSTRAINED) ) ON COLUMNS FROM [Referral Leads]))) WHERE ( IIF( STRTOSET(@.LeadSourceTypeLeadSourceType, CONSTRAINED).Count = 1, STRTOSET(@.LeadSourceTypeLeadSourceType, CONSTRAINED), [Lead Source Type].[Lead Source Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I'm afraid, given my user community, that if I can't get the date picker to work properly, it could be a deal breaker.

Thanks very much in advance for your help.

Regards,

Steve

Steve,

i've been there, done that, and it can be done.

For question 1, change your parameter type to a DateTime (no dataset required), and you will get the calendar control - you are getting a dropdown probably because you have got it as a string type and because you are supplying a dataset of values from the date heirarchy of your cube. The real trick to this is converting the output of the calendar control to something that resembles the members of your date heirarchy.

For question 2, you need to change the way your date is represented so that you can include it as a parameter in your MDX. See this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1480488&SiteID=1 for some actual examples i posted for someone else.

|||

Thanks for your help. I'm still missing something, because I'm still throwing an error. Could you take a look and see where I went wrong?

1. Opened the parameters created by the Wizard. Changed the Data type to DateTime. There are no boxes checked under Properties. Available values was changed to Non-queried; left the Label/Value matrix blank. Default values: Null.

2. Added Format(CDate(@.parameter),"yyyy-MM-ddT00:00:00".... to the MDX query. See below:

Code Snippet

SELECTNONEMPTY { [Measures].[Lead] } ONCOLUMNS, NONEMPTY { ([Store].[Store Region Type].[Store Region Type].ALLMEMBERS * [Store].[Store Region Name].[Store Region Name].ALLMEMBERS * [Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSIONPROPERTIESMEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWSFROM ( SELECT ( STRTOMEMBER(Format(CDate(@.FromLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00"), CONSTRAINED) : STRTOMEMBER(Format(CDate(@.ToLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00"), CONSTRAINED) ) ONCOLUMNSFROM [Referral Leads]) CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

3. For reference, I ran the query under the Dataset that the Wizard created for the parameter FoLeadCreationDateCalendarDate. The results of that query show the ParameterValue field to be populated with values of the format [LeadCreationDate].[Calendar Date].&[2000-01-02T00:00:00]. This is why I selected "yyyy-MM-ddT00:00:00" as my format string.

4. The error I am throwing is:

TITLE: Microsoft Visual Studio

Query preparation failed.


ADDITIONAL INFORMATION:

The following system error occurred: Type mismatch. . (Microsoft SQL Server 2005 Analysis Services)


BUTTONS:

OK

After re-reading your post, I also tried:

Code Snippet

SELECTNONEMPTY { [Measures].[Lead] } ONCOLUMNS, NONEMPTY { ([Store].[Store Region Type].[Store Region Type].ALLMEMBERS * [Store].[Store Region Name].[Store Region Name].ALLMEMBERS * [Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSIONPROPERTIESMEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWSFROM ( SELECT ( STRTOMEMBER("[LeadCreationDate].[Calendar Date].&[" + Format(CDate(@.FromLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) : STRTOMEMBER("[LeadCreationDate].[Calendar Date].&[" + Format(CDate(@.ToLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) ) ONCOLUMNSFROM [Referral Leads]) CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Also gave the same error.

Any help on this would be tremendously appreciated.

- Steve


|||

OK - it took me a few days but I figured out how to do it:

1. Develop the report using the Wizard.

2. As sluggy mentioned, change the Data Type of the date parameters to DateTime, and set Available Values to Non-queried.

3. On the Data tab, edit the cube's Dataset (click on the ... button).

4. Click on the Parameters tab of the Dataset dialog box. Replace the Value of the date parameters with ="[Dimension].[Member].&[" + Format(CDate(Parameters!ParameterName.Value), "yyyy-MM-ddT00:00:00") + "]" where [Dimension].[Member] and ParameterName are the dimensions and parameter names of your particular cube. Depending on how your dimension is set up, you may also need to change the format of the date.

5. There is no need to change the MDX of the query.

sql

Thursday, March 8, 2012

Date format used in expression

I am an Oracle PL/SQL guy, and am used to formatting dates with a to_date
conversion function. I can't figure out how to do this in RS.
I have a couple of date parameters, used to filter by date range, with the
default beginning value set at 1/1/1900 and the end value at 12/13/2050.
What I want to be able to do in the report header (where I display the
parameter values that the user inputs at runtime), is have "All" appear when
the user chooses the default values (i.e. the user is filtering by "all"
dates for that range). If the user actually chooses dates, then I show those
dates (which has worked pretty easily with teh following:
Parameters!CertRecordDtBegin.Value + " to " +
Parameters!CertRecordDtEnd.Value)
So I am trying to build an expression to display this in the header. I've
tried:
=IIf(Parameters!CertRecordDtBegin.Value = DATE(1900,1,1),
IIf(Parameters!CertRecordDtEnd.Value = DATE(2050,12,31), "All",
Parameters!CertRecordDtBegin.Value + " to " +
Parameters!CertRecordDtEnd.Value), Parameters!CertRecordDtBegin.Value + " to
" + Parameters!CertRecordDtEnd.Value)
I've also tried:
=IIf(FormatDateTime(Parameters!CertRecordDtBegin.Value, vbShortDate) = 01/01/1900, IIf(FormatDateTime(Parameters!CertRecordDtEnd.Value, vbShortDate)
= 12/31/2005, "All", Parameters!CertRecordDtBegin.Value + " to " +
Parameters!CertRecordDtEnd.Value), Parameters!CertRecordDtBegin.Value + " to
" + Parameters!CertRecordDtEnd.Value)
For the former, I got 2 errors:
- The value expression for the textbox contains an error: Input string was
not in a correct format, and
- The value expression for the textbox contains an error: [BC30108] 'Date'
is a type, and so is not a valid expression
For the latter, I don't get errors in my task list, but on the output, I get
"#Error" for that textbox.
Any ideas?
ThanksSee if any of these tips might help.
Case 1:
* Try using CDATE() or CSTR():
* CDATE(Parameters!CertRecordDtBegin.Value) // returns a Date type
* CSTR(DATE(1900,1,1)) // returns a string
* Use & instead of +
Case 2:
* Quote the dates to the right of the equals sign: "01/01/1900"
* Use & instead of +
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Derek in Richmond" <DerekinRichmond@.discussions.microsoft.com> wrote in
message news:4171551B-520F-4F8F-BA83-80FAAFF06252@.microsoft.com...
>I am an Oracle PL/SQL guy, and am used to formatting dates with a to_date
> conversion function. I can't figure out how to do this in RS.
> I have a couple of date parameters, used to filter by date range, with the
> default beginning value set at 1/1/1900 and the end value at 12/13/2050.
> What I want to be able to do in the report header (where I display the
> parameter values that the user inputs at runtime), is have "All" appear
> when
> the user chooses the default values (i.e. the user is filtering by "all"
> dates for that range). If the user actually chooses dates, then I show
> those
> dates (which has worked pretty easily with teh following:
> Parameters!CertRecordDtBegin.Value + " to " +
> Parameters!CertRecordDtEnd.Value)
> So I am trying to build an expression to display this in the header. I've
> tried:
> =IIf(Parameters!CertRecordDtBegin.Value = DATE(1900,1,1),
> IIf(Parameters!CertRecordDtEnd.Value = DATE(2050,12,31), "All",
> Parameters!CertRecordDtBegin.Value + " to " +
> Parameters!CertRecordDtEnd.Value), Parameters!CertRecordDtBegin.Value + "
> to
> " + Parameters!CertRecordDtEnd.Value)
> I've also tried:
> =IIf(FormatDateTime(Parameters!CertRecordDtBegin.Value, vbShortDate) => 01/01/1900, IIf(FormatDateTime(Parameters!CertRecordDtEnd.Value,
> vbShortDate)
> = 12/31/2005, "All", Parameters!CertRecordDtBegin.Value + " to " +
> Parameters!CertRecordDtEnd.Value), Parameters!CertRecordDtBegin.Value + "
> to
> " + Parameters!CertRecordDtEnd.Value)
> For the former, I got 2 errors:
> - The value expression for the textbox contains an error: Input string was
> not in a correct format, and
> - The value expression for the textbox contains an error: [BC30108] 'Date'
> is a type, and so is not a valid expression
> For the latter, I don't get errors in my task list, but on the output, I
> get
> "#Error" for that textbox.
> Any ideas?
> Thanks
>

Wednesday, March 7, 2012

Date Format Conversion

New to SQL and have a hopefully simple ?
I have a select statement that returns a date field in the yyyy-mm-dd
hh:mm:ss format. I would like to find a way to strip off the hh:mm:ss
portion and return the date only.
Thanks for the assistSee CONVERT() in Books Online:

select CONVERT(char(10), getdate(), 120)

You might also find this article useful for reference:

http://www.karaszi.com/sqlserver/info_datetime.asp

Simon|||You can use a proprietary CONVERT() function, or you can do the display
formatting in the front end like you are supposed to in a tiered
architecture.|||Thanks Simon/--Celko--

Date format Conversion

First of all, I'm fairly new to SQL Server 2000, so please be patient and explicit.

I have a text file that I'm going to import using DTS on a scheduled interval. The text file has three different date fields that are all formatted as:

YYYYMMDD example: 20031004

I need to get this data formated as:

MM/DD/YYYY, example 10/04/2003

DTS does not do this. If I set the field type to DATETIME it gives errors and will not import the data.

HELP!

Thanks,
Troy D. YoungHi, Set up a dts transform data task.
the go to th etransformation tab and remove the transformations that

cover datetime fileds.
Make suer yoiu have highlighted both the source and destination fields.
Add a new transform of type date.
Modify the date time formates and hit preview.
thats should do it.

regards, brian|||Originally posted by contiguous1
Hi, Set up a dts transform data task.
the go to th etransformation tab and remove the transformations that

cover datetime fileds.
Make suer yoiu have highlighted both the source and destination fields.
Add a new transform of type date.
Modify the date time formates and hit preview.
thats should do it.

regards, brian

It took some time, but I figured it out. For some reason it only lets me do one datetime field at a time in a transformation. I had to create a transformation for each datetime field in the table.

Thanks,
Troy D. Young

Sunday, February 19, 2012

Date conversion.

Is there a function I can use to retrieve the month and year of say getdate(
)?SELECT
DATEPART(MONTH, GETDATE()) [MonthColumn]
,DATEPART(YEAR, GETDATE()) [YearColumn]
,CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR(2)) + '/' +
CAST(DATEPART(MONTH, GETDATE()) AS Char(4)) [MonthAndYear]
Simon Worth
Arul wrote:
> Is there a function I can use to retrieve the month and year of say getdate()?[/co
lor]|||Sorry, typo
SELECT
DATEPART(MONTH, GETDATE()) [MonthColumn]
,DATEPART(YEAR, GETDATE()) [YearColumn]
,CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR(2)) + '/' +
CAST(DATEPART(YEAR, GETDATE()) AS Char(4)) [MonthAndYear]
Simon Worth
Simon Worth wrote:
> SELECT
> DATEPART(MONTH, GETDATE()) [MonthColumn]
> ,DATEPART(YEAR, GETDATE()) [YearColumn]
> ,CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR(2)) + '/' +
> CAST(DATEPART(MONTH, GETDATE()) AS Char(4)) [MonthAndYear]
> Simon Worth
> Arul wrote:
>|||Did you get any error?
Madhivanan|||Did I get an error?
No.
SELECT
DATEPART(MONTH, GETDATE()) [MonthColumn]
,DATEPART(YEAR, GETDATE()) [YearColumn]
,CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR(2)) + '/' +
CAST(DATEPART(YEAR, GETDATE()) AS Char(4)) [MonthAndYear]
MonthColumn,YearColumn,MonthAndYear
3,2005,3/2005
(1 row(s) affected)
Simon Worth
Madhivanan wrote:
> Did you get any error?
> Madhivanan
>

Date Conversion!

Can anyone suggest how to convert a dd/mm/yyyy format date to a mm/dd/yyyy
formatted date using a user defined function.
This funtion is to be called inside a stored procedure.
I have already written a conversion function that handle mm/dd/yyyy to
dd/mm/yyyy. Not sure how to go the other way.
I used date part for the mm/dd/yyyy coversion, but i assume it only
recocognises mm/dd/yyyy dates at parse time, so feeding it a dd/mm/yyyy date
won't do.
Help Appreciated.
AJDates are not stored in any particular format, so your question is simply hp
w
to format a date for display as mm/dd/yyyy. The convert function will do
this when passed a format specifier of 101.
Select convert(varChar(10), getdate(), 101)
103, by the way, formats any date as dd/mm/yyyy..
Select convert(varChar(10), getdate(), 103)
"AJ" wrote:

> Can anyone suggest how to convert a dd/mm/yyyy format date to a mm/dd/yyyy
> formatted date using a user defined function.
> This funtion is to be called inside a stored procedure.
> I have already written a conversion function that handle mm/dd/yyyy to
> dd/mm/yyyy. Not sure how to go the other way.
> I used date part for the mm/dd/yyyy coversion, but i assume it only
> recocognises mm/dd/yyyy dates at parse time, so feeding it a dd/mm/yyyy da
te
> won't do.
> Help Appreciated.
> AJ
>|||Hi
For more on date conversion & formatting issues, please refer to
http://msdn.microsoft.com/library/d.../>
ez_2h7w.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"CBretana" wrote:
> Dates are not stored in any particular format, so your question is simply
hpw
> to format a date for display as mm/dd/yyyy. The convert function will do
> this when passed a format specifier of 101.
>
> Select convert(varChar(10), getdate(), 101)
> 103, by the way, formats any date as dd/mm/yyyy..
> Select convert(varChar(10), getdate(), 103)
> "AJ" wrote:
>

Date Conversion throws truncation error

Hi,

I am trying to process a flat file feed. My date is in format of YYYYMMDD. The database column is of datatype "datetime". I have tried using all date related data types on FLAT FILE Connection Manager. I have also tried using Data Conversion Component. No luck so far!!

Any suggestion?

Thanks in advance,
-AnandTry the Derived Column Transformation.

This post doesn't solve exactly the same problem but its very very similar: http://blogs.conchango.com/jamiethomson/archive/2005/07/26/1867.aspx

-Jamie|||

The key point is, yyyymmdd cannot be casted to date type. (This has been bugged I believe, but feel free to log again it as I know I want it!). Using the Derived Column to crack it into format that can then be casted is the only route. I did test what was suppoted and what wasn't, but seem to have lost the packages for now....

I would also check you cracked format works for US/UK locales, as the flip-flop between the two can really mess you up. I like dd mmm yyyy myself, as no languages muck that up, not even VBScript! This may help as a start if you follow that format-

(MONTH(RowDate) == 1 ? "January" : MONTH(RowDate) == 2 ? "February" : MONTH(RowDate) == 3 ? "March" : MONTH(RowDate) == 4 ? "April" : MONTH(RowDate) == 5 ? "May" : MONTH(RowDate) == 6 ? "June" : MONTH(RowDate) == 7 ? "July" : MONTH(RowDate) == 8 ? "August" : MONTH(RowDate) == 9 ? "September" : MONTH(RowDate) == 10 ? "October" : MONTH(RowDate) == 11 ? "November" : MONTH(RowDate) == 12 ? "December" : "InvalidMonth")

|||Hi,

I have partial success with "Data Conversion". What should I do for the column which may contain '' or null?

One more thing: Is dt_dbdate in SSIS compatible with datetime in SQL Server 2005?

-Anand|||Hi,

Will this work in the "Derived Column"?

ISNULL([Maturity Date]) ? : (DT_DBDATE)(SUBSTRING(TRIM([Maturity Date]),5,2) + SUBSTRING(TRIM([Maturity Date]),6,2) + SUBSTRING(TRIM([Maturity Date]),1,4))|||I have resolved the issue using following "Derived Column" statement:

(TRIM([Maturity Date]) == "") ? NULL(DT_WSTR,8) : SUBSTRING(TRIM([Maturity Date]),5,2) + "/" + SUBSTRING(TRIM([Maturity Date]),7,2) + "/" + SUBSTRING(TRIM([Maturity Date]),1,4)

Cheers,
Anand

Big Smile

Date Conversion question... need a yearly date range

Hi all,
I am writing a query that is doing a yearly breakdown.
I've been looking at the SQL date funcitons, but am a little confused
as to how best accomplish this.
Basically I want the query to get info from the 1-Jan this year, to
the 31-Dec this year.
If the SQL query is run in two years time, it needs to automatically
get the date range for that particular year.
(ie. I can't hardcode the year in the sql statement)
I've been messing around with getdate()... I can get the year
component of getdate... but I'm not sure how to then use that in my
query to do something like
where dateadded >= '01-Jan-'+year(getdate())
(I know the above it wrong but it shows what im trying to achieve..)
Any info would be very helpful
Thanks!!!Hello,
Use CONVERT Function. see the below sample.
where dateadded >= '01-Jan-'+convert(char,year(getdate())) and dateadded
<='31-Dec-'+convert(char,year(getdate()))
Thanks
Hari
"David Conte" <davconts@.gmail.com> wrote in message
news:1170642986.078542.244700@.a75g2000cwd.googlegroups.com...
> Hi all,
> I am writing a query that is doing a yearly breakdown.
> I've been looking at the SQL date funcitons, but am a little confused
> as to how best accomplish this.
> Basically I want the query to get info from the 1-Jan this year, to
> the 31-Dec this year.
> If the SQL query is run in two years time, it needs to automatically
> get the date range for that particular year.
> (ie. I can't hardcode the year in the sql statement)
> I've been messing around with getdate()... I can get the year
> component of getdate... but I'm not sure how to then use that in my
> query to do something like
> where dateadded >= '01-Jan-'+year(getdate())
> (I know the above it wrong but it shows what im trying to achieve..)
> Any info would be very helpful
> Thanks!!!
>|||On Feb 5, 2:11 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:[vbcol=seagreen]
> Hello,
> Use CONVERT Function. see the below sample.
> where dateadded >= '01-Jan-'+convert(char,year(getdate())) and dateadded
> <='31-Dec-'+convert(char,year(getdate()))
> Thanks
> Hari
> "David Conte" <davco...@.gmail.com> wrote in message
> news:1170642986.078542.244700@.a75g2000cwd.googlegroups.com...
>
>
>
>
>
>
>
>
ahh excellent - thought I was missing a step! :D
I'll give it a try.
Thanks very much for the prompt reply Hari!

Date Conversion question... need a yearly date range

Hi all,
I am writing a query that is doing a yearly breakdown.
I've been looking at the SQL date funcitons, but am a little confused
as to how best accomplish this.
Basically I want the query to get info from the 1-Jan this year, to
the 31-Dec this year.
If the SQL query is run in two years time, it needs to automatically
get the date range for that particular year.
(ie. I can't hardcode the year in the sql statement)
I've been messing around with getdate()... I can get the year
component of getdate... but I'm not sure how to then use that in my
query to do something like
where dateadded >= '01-Jan-'+year(getdate())
(I know the above it wrong but it shows what im trying to achieve..)
Any info would be very helpful
Thanks!!!
Hello,
Use CONVERT Function. see the below sample.
where dateadded >= '01-Jan-'+convert(char,year(getdate())) and dateadded
<='31-Dec-'+convert(char,year(getdate()))
Thanks
Hari
"David Conte" <davconts@.gmail.com> wrote in message
news:1170642986.078542.244700@.a75g2000cwd.googlegr oups.com...
> Hi all,
> I am writing a query that is doing a yearly breakdown.
> I've been looking at the SQL date funcitons, but am a little confused
> as to how best accomplish this.
> Basically I want the query to get info from the 1-Jan this year, to
> the 31-Dec this year.
> If the SQL query is run in two years time, it needs to automatically
> get the date range for that particular year.
> (ie. I can't hardcode the year in the sql statement)
> I've been messing around with getdate()... I can get the year
> component of getdate... but I'm not sure how to then use that in my
> query to do something like
> where dateadded >= '01-Jan-'+year(getdate())
> (I know the above it wrong but it shows what im trying to achieve..)
> Any info would be very helpful
> Thanks!!!
>
|||On Feb 5, 2:11 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:[vbcol=seagreen]
> Hello,
> Use CONVERT Function. see the below sample.
> where dateadded >= '01-Jan-'+convert(char,year(getdate())) and dateadded
> <='31-Dec-'+convert(char,year(getdate()))
> Thanks
> Hari
> "David Conte" <davco...@.gmail.com> wrote in message
> news:1170642986.078542.244700@.a75g2000cwd.googlegr oups.com...
>
>
>
>
ahh excellent - thought I was missing a step! :D
I'll give it a try.
Thanks very much for the prompt reply Hari!

Date Conversion question... need a yearly date range

Hi all,
I am writing a query that is doing a yearly breakdown.
I've been looking at the SQL date funcitons, but am a little confused
as to how best accomplish this.
Basically I want the query to get info from the 1-Jan this year, to
the 31-Dec this year.
If the SQL query is run in two years time, it needs to automatically
get the date range for that particular year.
(ie. I can't hardcode the year in the sql statement)
I've been messing around with getdate()... I can get the year
component of getdate... but I'm not sure how to then use that in my
query to do something like
where dateadded >= '01-Jan-'+year(getdate())
(I know the above it wrong but it shows what im trying to achieve..)
Any info would be very helpful
Thanks!!!Hello,
Use CONVERT Function. see the below sample.
where dateadded >= '01-Jan-'+convert(char,year(getdate())) and dateadded
<='31-Dec-'+convert(char,year(getdate()))
Thanks
Hari
"David Conte" <davconts@.gmail.com> wrote in message
news:1170642986.078542.244700@.a75g2000cwd.googlegroups.com...
> Hi all,
> I am writing a query that is doing a yearly breakdown.
> I've been looking at the SQL date funcitons, but am a little confused
> as to how best accomplish this.
> Basically I want the query to get info from the 1-Jan this year, to
> the 31-Dec this year.
> If the SQL query is run in two years time, it needs to automatically
> get the date range for that particular year.
> (ie. I can't hardcode the year in the sql statement)
> I've been messing around with getdate()... I can get the year
> component of getdate... but I'm not sure how to then use that in my
> query to do something like
> where dateadded >= '01-Jan-'+year(getdate())
> (I know the above it wrong but it shows what im trying to achieve..)
> Any info would be very helpful
> Thanks!!!
>|||On Feb 5, 2:11 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> Use CONVERT Function. see the below sample.
> where dateadded >= '01-Jan-'+convert(char,year(getdate())) and dateadded
> <='31-Dec-'+convert(char,year(getdate()))
> Thanks
> Hari
> "David Conte" <davco...@.gmail.com> wrote in message
> news:1170642986.078542.244700@.a75g2000cwd.googlegroups.com...
> > Hi all,
> > I am writing a query that is doing a yearly breakdown.
> > I've been looking at the SQL date funcitons, but am a little confused
> > as to how best accomplish this.
> > Basically I want the query to get info from the 1-Jan this year, to
> > the 31-Dec this year.
> > If the SQL query is run in two years time, it needs to automatically
> > get the date range for that particular year.
> > (ie. I can't hardcode the year in the sql statement)
> > I've been messing around with getdate()... I can get the year
> > component of getdate... but I'm not sure how to then use that in my
> > query to do something like
> > where dateadded >= '01-Jan-'+year(getdate())
> > (I know the above it wrong but it shows what im trying to achieve..)
> > Any info would be very helpful
> > Thanks!!!
ahh excellent - thought I was missing a step! :D
I'll give it a try.
Thanks very much for the prompt reply Hari!

date conversion question

i have 2 fields that i want to concatenate and then convert it's date format. one field is a smalldatetime, the other is a varchar:

u_Departure_Time = varchar(5), ex data is 05:30 or 16:30
EffFrom = smalldatetime, ex data is 09/01/2003

trying to do something like this:
CONVERT(datetime,(dateadd(dd, value, EffFrom)+' '+u_Departure_Time),'mm/dd/yyyy hh:mi:ms AM')

i need to take the varchar 24hr time from u_Departure_Time, concatenate it to EffFrom and make it 12hr format. In the above examples I'd like to obtain:

9/1/2003 5:30:00 AM
9/1/2003 4:30:00 PM

it seems basic, but i'm an oracle guy, just started using sql server.

thanks!select cast(convert(char(10), EffFrom, 101) + ' ' + u_Departure_Time as datetime)|||Try this one:

declare @.date smalldatetime,@.time varchar(10)
set @.date='09/17/2003'
set @.time='16:30'
select cast(@.date+' '+@.time as datetime)|||Originally posted by ms_sql_dba
select cast(convert(char(10), EffFrom, 101) + ' ' + u_Departure_Time as datetime)

cool, i tried this:
,cast(convert(char(10), EffFrom, 101) + ' ' + u_Departure_Time as smalldatetime)

but it's in 24hr time,

sample: 2003-08-01 19:25:00

i need it to match another tables format of dd/mm/yyyy h:mm:ss AM/PM

like: 2003-08-01 7:25:00 PM

this is really a newbie question, but what does the '101' signify?|||101 converts 2003-09-01 to 09/01/2003 format so that you can att time portion to it before converting it back to datetime.

Date Conversion Problem

Hi,

I've got a table with the columns DAY, MONTH and YEAR, all of them are numeric. I want to join them and store in a single datetime column.

I've been trying several ways to do that, but unfortunately I receive the same error :

"[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Probably I don't know how to use the CONVERT/CAST function properly.

Any ideas?

(Thanx)No, this error normally means that you are trying to convert values that are not an actual date.

11/31/03, or 2/29/2003, or 13/05/04, for example.

This happens when you have a database that does not store values in datetime format. It can also happen if you supply a day, month, and year value to SQL server in 2-digit formats and SQL server is unsure which value represents which part of the the date.

What code are you using for your conversion? You could wrap the ISDATE() function around it and query against your source table to find any values that it cannot convert.|||CAST(columnMonth AS Char(2)) + '/' + CAST(columnDay AS Char(2)) + '/' + CAST(columnYear AS Char(4))

returns the string which can be inserted into the field with DATETIME datatype|||STR(columnMonth, 2) + '/' + STR(columnDay, 2) + '/' + STR(columnYear, 4)

should work too.

Date Conversion Problem

I am importing a text file into a SQL table, using DTS. My problem is concerning the date fields. The source fields are in the yyyymmdd format. I have tried using datetime transformation, using yyyyMMdd as the source format, and MM/dd/yyyy as the destination formation. If there is a valid date, this works fine. However, many of the dates are either null or contain spaces, and the DTS will not handle them. Any suggestions as to how to handle this?Is the column defined as NOT NULL?

DTS the table a stage table with all of the columns a varchar...

Then manipulate it with sql and do an insert?|||Originally posted by Brett Kaiser
Is the column defined as NOT NULL?

DTS the table a stage table with all of the columns a varchar...

Then manipulate it with sql and do an insert?

-----------

The Allow Nulls option is turned on in the table definition. If I manually add a record in Enterprise Manager, it will accept nulls. It's just the DTS that doesn't like them.

I can use an intermediary table if that's the only way. I was just hoping that it could be done during the initial import.

Thanks for your suggestions.|||Sounds like a fixed width file...

Actually I' m suprised it's not working...

Where's the file coming from?

Mainframe?

Got any unprintable chars there?|||Originally posted by Brett Kaiser
Sounds like a fixed width file...

Actually I' m suprised it's not working...

Where's the file coming from?

Mainframe?

Got any unprintable chars there?

Yes, it is a mainframe file, with fixed width fields. There are no unprintable characters. It's just that some of the date fields are either null or contain spaces (I'm not sure which), and DTS keeps choking on them.|||Can you post the transformation code?

You do know that putting the code in the package like that slows everything down..

You're much better off getting all the data in, then using set based methods to transform the data...

much, much fatser...

Ever use bcp?|||Originally posted by Brett Kaiser
Can you post the transformation code?

You do know that putting the code in the package like that slows everything down..

You're much better off getting all the data in, then using set based methods to transform the data...

much, much fatser...

Ever use bcp?

No, I haven't used bcp before. I'll check it out.

In addition to choosing datetime transformation and setting the formats, I've also tried using an ActiveX script. Here is the ActiveX code I've tried for the transformation:

Function Main()
If Not IsNull(DTSSource("Col010")) AND LEN(TRIM(DTSSource ("Col010"))) > 0 Then (Checking for null or spaces)
DTSDestination("AWARD_DATE") = MID(DTSSource("Col010"),7,2)&"-"&MID(DTSSource("Col010"),5,2)&"-"&LEFT(DTSSource("Col010"),4)
Main = DTSTransformStat_OK
End If
End Function

The error returned is: Invalid procedure call or argument - DTSSource|||Just wondering is your System a AS400 cause i had also ran into this before.|||Originally posted by hillcat
Just wondering is your System a AS400 cause i had also ran into this before.

No, PC with Windows XP Pro & SQL Server 2000|||but is the the mainframe file a rpg file|||Originally posted by hillcat
but is the the mainframe file a rpg file

I'm not familiar with rpg; all I know is, the file is a text file from a mainframe, with fixed width fields. I was given a printout of the file layout to indicate starting and ending point of the fields.|||well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.|||Originally posted by hillcat
well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.

I'm not sure whether it is null or spaces; that's why I used both the the 'not isnull' and the 'trim', so that I'd be covered either way. If either is not true (value is null, or value is spaces), then the statements inside the if clause should be bypassed|||My guess here is that if you DTS a column that has space and no transformation, it'll put in null..

But because of the transformation, I guess it thinks there should be a valid value, and then fails.

The other thing is that it might not be space, but other data that doesn't transform to a valid date.

Use a stage table and do some analysis.

Soemthing like

SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0|||Originally posted by Brett Kaiser
My guess here is that if you DTS a column that has space and no transformation, it'll put in null..

But because of the transformation, I guess it thinks there should be a valid value, and then fails.

The other thing is that it might not be space, but other data that doesn't transform to a valid date.

Use a stage table and do some analysis.

Soemthing like

SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0

I'll give it a try. Much thanks . . .|||Like:

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(8))
GO

INSERT INTO myTable99(Col1)
SELECT 'yyyymmdd' UNION ALL
SELECT '20040317' UNION ALL
SELECT ' '
GO

-- Show me Valid Dates
SELECT * FROM myTable99 WHERE ISDATE(Col1)=1

-- Show me InValid Dates
SELECT * FROM myTable99 WHERE ISDATE(Col1)=0

--Move to it's Final Destination

CREATE TABLE myTable00(Col1 datetime)
GO

INSERT INTO myTable00(Col1)
SELECT Col1 FROM myTable99 WHERE ISDATE(Col1)=1

SELECT * FROM myTable00
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO

Date Conversion inconsistent with DB rules

I'm trying to validate data entered from users as legitimate dates by running them thru a conversion component. When conversion fails, I asssign NULLs, other wise land the date to a datetime field on the database. Dates that pass thru the conversion, however fail on insert into the DB.

It appears that the rules in the conversion component are different than that of the DB?

for instance if a user forgot the last digit on a year, i.e. "10/22/197" the convert creates a field for any of the various date datatypes as 10/22/0197, which fails on imsert into the DB, becasue it won't allow years prior to 1753.

Dates are very piccy, it is best to validate and make sure the date is correct in your package, in addition some rdms support dates before 1753 which is why SSIS supports them, SQL doesn't.

Date conversion in SSIS ETL

I have date in Flat file and it is in the string format,but now i want to convert it in to normal date format.I have tried doing this by SSIS but it is not working.Are you trying to use a data conversion transform? What error are you getting?|||yes in Data flow transformation in intregartion services.I have numerously but i have not succeded|||and dat is with Double quotes.|||Please post an example of the date data and we'll try to help you from there.|||

This is example of flat file in .csv

890512 means 05/12/1989 .

When i uploda using SSIS 890512 becomes "890512" and data type is string.

I want to convert "890512" to date format but i m getting error.

|||You shouldn't have the double quotes in your data when reading a .CSV file into SSIS. If you do, then perhaps there's an error in how you set up your flat file import. Make sure the text qualifier is set to double quotes.

But before we give an example of how to work with your non-year 2000 compliant date format, what are the rules for prefixing the year with "19" or "20"?|||What is to be given in SSIS text qualifier.I can not get it.|||

Nirad Pachchigar wrote:

What is to be given in SSIS text qualifier.I can not get it.

"|||

now uploading is fine without "",but still i can not convert date into proper format.i got error on say after 6514 column.

Error: 0xC02020C5 at Data Flow Task, Data Conversion [1]: Data conversion failed while converting column "DATE" (60) to column "Copy of DATE" (249). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Data Flow Task, Data Conversion [1]: The "output column "Copy of DATE" (249)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of DATE" (249)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (1) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

|||

Phil Brammer wrote:

You shouldn't have the double quotes in your data when reading a .CSV file into SSIS. If you do, then perhaps there's an error in how you set up your flat file import. Make sure the text qualifier is set to double quotes.

But before we give an example of how to work with your non-year 2000 compliant date format, what are the rules for prefixing the year with "19" or "20"?

You can't convert the date as it is, you'll need to format your string. To do that, you should be using a 4-digit year as well. Answering my previous question will help ensure you do it correctly.

However, this is how you get started:

(DT_DBDATE)(substring([datecolumn],3,2) + "/" + substring([datecolumn],5,2) + "/" + substring([datecolumn],1,2))|||

See this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=506809&SiteID=1. It shows an example of using SUBSTRING to grab the components of the date from a string and convert to a date. As Phil mentioned above, you'll need to add some logic to handle what century the date belongs in.

(DT_DATE)(SUBSTRING(YourDate,3,2) + "/" + SUBSTRING(YourDate,5,2) + "/" + (SUBSTRING(YourDate,1,2) < 40 : ("20" + SUBSTRING(YourDate,1,2)), ("19" + SUBSTRING(YourDate,1,2)))

Not sure if got the syntax exactly right (don't have the IDE available right now), but it should be close.

|||

jwelch wrote:

See this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=506809&SiteID=1. It shows an example of using SUBSTRING to grab the components of the date from a string and convert to a date. As Phil mentioned above, you'll need to add some logic to handle what century the date belongs in.

(DT_DATE)(SUBSTRING(YourDate,3,2) + "/" + SUBSTRING(YourDate,5,2) + "/" + (SUBSTRING(YourDate,1,2) < 40 : ("20" + SUBSTRING(YourDate,1,2)), ("19" + SUBSTRING(YourDate,1,2)))

Not sure if got the syntax exactly right (don't have the IDE available right now), but it should be close.

Real close, John. And this assumes that when the year is 0-39, you prefix it with "20" or else you prefix with "19".

Here's the corrected syntax:
(DT_DBDATE)((SUBSTRING(YourDate,3,2) + "/" +

SUBSTRING(YourDate,5,2) + "/" + ((SUBSTRING(YourDate,1,2) < 40 ?

("20" + SUBSTRING(YourDate,1,2)) : ("19" + SUBSTRING(YourDate,1,2)))))

Here's a question for the OP though. Since this is a numeric number stored as an integer and without the century indicator for the year, what happens when we are talking about March 25, 2007? Is the date listed as 70325, or 070325? This will make a difference in how you convert the date.

Date Conversion in SQL Server

Hi folks,

Here are the fields I have

eventDate = smallDateTime 2005-12-12 00:00:00
eventTime = varchar(20) 1:00:00
newEventDate = dateTime [desired result: 2005-12-12 1:00:00]

When I run the following script:
update healthEvent
set newEventDate = cast(substring(convert(varchar,eventDate,120),1,10 )+' '+eventTime as DateTime)

I get "Syntax error converting datetime from character string." error.

Any ideas why?

Thanks!

-Parul-- Comment
What in the name of all that is relational and logical would somebody design a POS like this?

-- Answer
Because you are not putting them together properly.

The date string at 1 AM is like this:
2007-05-17 01:00:00.000

The date string at 1 PM is like this:
2007-05-17 13:00:00.000

You are trying to slam the 1 against the space after the date without the leading zero.

Start from the inside and work out. When I convert, I like to explicitly define the size of my char variable inside of the convert instead of letting a varchar guess at what i want (convert(char(10),eventDate,120).

In this case I would use an 11 char convert to pick up the space after the date portion plus a 0 plus the ltrim(rtrim(time)) component.


declare @.eventDate smallDateTime
select @.eventDate = '2005-12-12 00:00:00'
declare @.eventTime varchar(20)
select @.eventTime = '1:00:00'
declare @.newEventDate dateTime --[desired result: 2005-12-12 1:00:00]
set @.newEventDate = cast(substring(convert(varchar,@.eventDate,120),1,1 0 )+' 0'+@.eventTime as DateTime)
select @.newEventDate
-- Results
----------------
2005-12-12 01:00:00.000
(1 row(s) affected)|||Thanks so much for your reply.

Unfortunatley, I am still getting the same error as I try to insert @.newEventDate into another date field.|||Plus do I need that leading zero at all times? What if I have @.eventTime as '11:00:00'|||You will have to do a CASE statement for your time segment on the datalength of the ltrim(rtrim(time segment)) . If len = 5 add the zero else don't.

Date Conversion help

Hi,

I have my dates in DB2 source in two formats -

Format 1 - char(5) - Example - 10305. 1 indicates century,03 indicates year and 05 indicates month. The day is not stored. So this is 2003,May 1

Format 2 - char(7) - Example - 1030525. 1 indicates century,03 indicates year, 05 indicates month,25 indicates day. 2003,May 25

I want to convert the above two formats to SQL Server smalldatetime and I only need the DATEPART. The date needs to be in the format mm/dd/yyyy. The default day would be 01 when the day is not specified.

If the format is 00305 then the 0 indicated 19th century. So this is 1903, May 1.

Any help is appreciated.

Thanks,

Vivek1 indicates century? What century is it supposed to indicate? Are we talking the year 1903? I am assuming it is supposed to be 1=2000 in the code below:

CREATE FUNCTION fn_convert_DB@._date (@.db2_date VARCHAR(7))

RETURNS SMALLDATETIME AS

DECLARE @.new_date AS SMALLDATETIME

IF LEN(@.db2_date) = 5
SELECT @.new_date = CAST(RIGHT(@.db2_date,2) + '/1/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 1 AS VARCHAR(1)) + '0' + SUBSTRING(@.db2_date, 2, 2) AS DATETIME)

IF LEN(@.db2_date) = 7
SELECT @.new_date = CAST(SUBSTRING(@.db2_date,4, 2) + '/' + RIGHT(@.db2_date, 2) + '/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 1 AS VARCHAR(1)) + '0' + SUBSTRING(@.db2_date, 2, 2) AS DATETIME)

RETURN @.new_date|||Yes 1 indicates century 2000 and 0 indicated 1900. So 10305 would be 2003. From first 3 digits. And 19505 would be 1995.|||You should be able to use the function inline in your queries:

SELECT dbo.fn_convert_db2_date(the_db2_date_field)

will return the converted SMALLDATETIME field|||Hi,

I am not able to convert 09525 using the function. Also the date I need is of the format - mm/dd/yyyy ex: 5/21/2003. When I execute the function for '10325', I get 2004-05-01 00:00:00. I do not need the time part. Also the format is different.

Thanks|||The date format is always store in the same fashion in SQL Server. What you want to do is alter the format when you display the date in your UI/report/whatever it is.

CONVERT(the_date_field, 101) will give you a VARCHAR of the date in mm/dd/yyyy.

'10325', according to your definition, would be Year=2003, Month = 25.

Reworking the year conversion. Sorry about the shoddy workmanship there.|||CREATE FUNCTION fn_convert_DB@._date (@.db2_date VARCHAR(7))

RETURNS SMALLDATETIME AS

DECLARE @.new_date AS SMALLDATETIME

IF LEN(@.db2_date) = 5 AND ISDATE(RIGHT(@.db2_date,2) + '/1/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2))=1
SELECT @.new_date = CAST(RIGHT(@.db2_date,2) + '/1/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2) AS SMALLDATETIME)

IF LEN(@.db2_date) = 7 AND ISDATE(SUBSTRING(@.db2_date,4, 2) + '/' + RIGHT(@.db2_date, 2) + '/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2))=1
SELECT @.new_date = CAST(SUBSTRING(@.db2_date,4, 2) + '/' + RIGHT(@.db2_date, 2) + '/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2) AS DATETIME)

RETURN @.new_date|||New function checks for proper date format before converting, and returns NULL if format is wrong. Oh, and it deals properly with centuries.

Date Conversion Help

Here is my query
SELECT TOP 1000 Team, Segment, Associate, SUM([Adjusted Weight]) AS
[SumOfAdjusted Weight], SUM(ExtSatWt) AS SumOfExtSatWt,
SUM(VerSatWt) AS SumOfVerSatWt, COUNT([Surv Strt Tm])
AS [CountOfSurv Strt Tm], [Date]
FROM dbo.CSI
WHERE ([Date] >= 'dbo.Getparams.startdate') AND ([Date] <= 'dbo.Getparams.
Enddate')
I'm trying to pass the startdate from the table to my query and get a syntax
error converting character string to smalldatetime data. I have changed the
startdate field in the getparams table to Char, then datetime, then Varchar
and tried convert and replace convert, and still not getting it to pass. Yet
if I hard code the date say '05/01/2006' (which is the same as in the table)
the query works fine.
What to do? Thanks for your help in advance!!!
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1> SELECT TOP 1000 Team, Segment, Associate, SUM([Adjusted Weight]) AS
> [SumOfAdjusted Weight], SUM(ExtSatWt) AS SumOfExtSatWt,
> SUM(VerSatWt) AS SumOfVerSatWt, COUNT([Surv Strt Tm])
> AS [CountOfSurv Strt Tm], [Date]
> FROM dbo.CSI
> WHERE ([Date] >= 'dbo.Getparams.startdate') AND ([Date] <=
> 'dbo.Getparams.
> Enddate')

> I'm trying to pass the startdate from the table to my query and get a
> syntax
> error converting character string to smalldatetime data.
If you have a single row in th GetParams table, then try:
DECLARE @.sd SMALLDATETIME, @.ed SMALLDATETIME;
SELECT @.sd = StartDate, @.ed = EndDate FROM dbo.GetParams;
SELECT ... WHERE [Date] >= @.sd AND [Date] <= @.ed;
The way you've shoved a reference to a table name into a place that expects
a scalar value (or a column that comes from a table that actually
participates in the query) does not work.
Also, what TOP 1000 do you expect? Unless you don't care that you could get
a different result and in a different order every time you run the query,
please don't use TOP without ORDER BY. I'd also suggest avoiding the use of
reserved words or words with spaces as column names.

> if I hard code the date say '05/01/2006' (which is the same as in the
> table)
NO IT IS NOT! SQL Server does not store such a regional and ambiguous
format. In fact, it does not store a string at all, but rather a pair of
integers.
http://www.karaszi.com/SQLServer/info_datetime.asp|||It takes a SELECT statement to get data out of a table. Your
dbo.GetParams.StartDate and dbo.GetParams.EndDate are just idle references
at this point.
You could either obtain the values and put them in variables, or perhaps
create a function that would get them for you. (The function is useful if
you often need the Start/End dates from the GetParams table.
Arnie Rowland, PhD
"To be successful, your heart must accompany your knowledge."
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:62833dcfeea64@.uwe...
> Here is my query
> SELECT TOP 1000 Team, Segment, Associate, SUM([Adjusted Weight]) AS
> [SumOfAdjusted Weight], SUM(ExtSatWt) AS SumOfExtSatWt,
> SUM(VerSatWt) AS SumOfVerSatWt, COUNT([Surv Strt Tm])
> AS [CountOfSurv Strt Tm], [Date]
> FROM dbo.CSI
> WHERE ([Date] >= 'dbo.Getparams.startdate') AND ([Date] <=
> 'dbo.Getparams.
> Enddate')
> I'm trying to pass the startdate from the table to my query and get a
> syntax
> error converting character string to smalldatetime data. I have changed
> the
> startdate field in the getparams table to Char, then datetime, then
> Varchar
> and tried convert and replace convert, and still not getting it to pass.
> Yet
> if I hard code the date say '05/01/2006' (which is the same as in the
> table)
> the query works fine.
> What to do? Thanks for your help in advance!!!
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1

Date conversion error on 2005

Getting following error on 2005 on a query that works fine on 2000:
---
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
---
Here is the query: T_DATE column datatype is varchar(30) and the table does
have some rows with non-date data (zero) outside of the where clause.
---
SELECT col_names
FROM tablename
WHERE
CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
---
Query works fine after I run the following update:
----
update tablename set T_DATE = null where isdate(T_DATE) = 0
---
Is there any way we could make it work as-is, the way it was running in 2000
without any changes."Amit" <amitjn_ca@.yahoo.ca> wrote in message
news:O$OrWfIGGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Getting following error on 2005 on a query that works fine on 2000:
> ---
> Msg 241, Level 16, State 1, Line 1
> Conversion failed when converting datetime from character string.
> ---
> Here is the query: T_DATE column datatype is varchar(30) and the table
> does have some rows with non-date data (zero) outside of the where clause.
> ---
> SELECT col_names
> FROM tablename
> WHERE
> CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
> ---
> Query works fine after I run the following update:
> ----
> update tablename set T_DATE = null where isdate(T_DATE) = 0
> ---
> Is there any way we could make it work as-is, the way it was running in
> 2000 without any changes.
>
If this works in 2000 and not in 2005 then check that the LANGUAGE and
DATEFORMAT settings are the same in each case. On my system I get the
"conversion failed" or "syntax error" in both versions when trying to
convert the string '0'.
CASE should prove more reliable. See the following example and notice that
I've specified a value for the style parameter of the CONVERT function - do
the same if you can and use LIKE to find valid dates rather than rely on the
implicit conversions that ISDATE uses.
CREATE TABLE tablename (t_date VARCHAR(30));
INSERT INTO tablename VALUES ('0');
SELECT t_date
FROM
(SELECT CASE WHEN ISDATE(t_date)=1 THEN t_date END AS t_date
FROM tablename) AS T
WHERE CONVERT(DATETIME, t_date,1) < DATEADD(d,7, GETDATE());
Result:
(1 row(s) affected)
t_date
--
(0 row(s) affected)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Date conversion error on 2005

Getting following error on 2005 on a query that works fine on 2000:
---
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
---
Here is the query: T_DATE column datatype is varchar(30) and the table does
have some rows with non-date data (zero) outside of the where clause.
---
SELECT col_names
FROM tablename
WHERE
CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
---
Query works fine after I run the following update:
----
update tablename set T_DATE = null where isdate(T_DATE) = 0
---
Is there any way we could make it work as-is, the way it was running in 2000
without any changes."Amit" <amitjn_ca@.yahoo.ca> wrote in message
news:O$OrWfIGGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Getting following error on 2005 on a query that works fine on 2000:
> ---
> Msg 241, Level 16, State 1, Line 1
> Conversion failed when converting datetime from character string.
> ---
> Here is the query: T_DATE column datatype is varchar(30) and the table
> does have some rows with non-date data (zero) outside of the where clause.
> ---
> SELECT col_names
> FROM tablename
> WHERE
> CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
> ---
> Query works fine after I run the following update:
> ----
> update tablename set T_DATE = null where isdate(T_DATE) = 0
> ---
> Is there any way we could make it work as-is, the way it was running in
> 2000 without any changes.
>
If this works in 2000 and not in 2005 then check that the LANGUAGE and
DATEFORMAT settings are the same in each case. On my system I get the
"conversion failed" or "syntax error" in both versions when trying to
convert the string '0'.
CASE should prove more reliable. See the following example and notice that
I've specified a value for the style parameter of the CONVERT function - do
the same if you can and use LIKE to find valid dates rather than rely on the
implicit conversions that ISDATE uses.
CREATE TABLE tablename (t_date VARCHAR(30));
INSERT INTO tablename VALUES ('0');
SELECT t_date
FROM
(SELECT CASE WHEN ISDATE(t_date)=1 THEN t_date END AS t_date
FROM tablename) AS T
WHERE CONVERT(DATETIME, t_date,1) < DATEADD(d,7, GETDATE());
Result:
(1 row(s) affected)
t_date
--
(0 row(s) affected)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--