Cast from Integer to Date error

[RL_Boston]

Hi all - This is a strange issue i'm having, I have been doing this conversion from an INT (YYYYMMDD) to Date using CAST in my model for a while now.
Now I'm in the middle of updating the model and I'm getting this error:

UDA-SQL-0115 Inappropriate SQL request.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Deferred prepare could not be completed.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Statement(s) could not be prepared. (SQLSTATE=42000, SQLERRORCODE=8180)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Explicit conversion from data type int to date is not allowed.

The strange part is that in FM, the query is valid and appears correctly in the "test" tab. 

Any Ideas? thanks in advance!

[CognosPaul]

Just a guess:

SQL Server has issues with date format. Date is actually a data type that was first introduced in 2008, and as such some older drivers and validation software are still buggy with it. Instead, try casting to datetime. You can use data format to hide the time portion.

[cognostechie]

Hi all - This is a strange issue i'm having, I have been doing this conversion from an INT (YYYYMMDD) to Date using CAST in my model for a while now.
Now I'm in the middle of updating the model and I'm getting this error:

UDA-SQL-0115 Inappropriate SQL request.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Deferred prepare could not be completed.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Statement(s) could not be prepared. (SQLSTATE=42000, SQLERRORCODE=8180)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Explicit conversion from data type int to date is not allowed.

The strange part is that in FM, the query is valid and appears correctly in the "test" tab. 

Any Ideas? thanks in advance!

I think it's because now SQL Server allows date in many formats so it can't presume that the 1st 4 digits of the integer date field will always be the year. To be 100% proof, I always convert it in a way that it will know what to expect from every character. Try cating it to char and then using substring, cast it to date.

cast(
substring(  cast([Database Layer].[Date_Dim].[Date_Key]  , varchar(8)),1,4) || '-' ||
substring(  cast([Database Layer].[Date_Dim].[Date_Key]  , varchar(8)),5,2) || '-' ||
substring(  cast([Database Layer].[Date_Dim].[Date_Key]  , varchar(8)),7,2)
,date)

That way it will always recieve YYYY followed by MM and then DD.

[sir_jeroen]

have you tried CONVERT(date,CAST(20130101 as varchar(8)), 112) (First cast int to string)
See http://msdn.microsoft.com/en-us/library/ms187928.aspx

[cognostechie]

@ OP - That 'cool'  smiley means 8 !!     8) 8) 8) 8)

[Satheesh]

HI,


This error indicates that the transaction log is full or requires optimizing on the database.
[/size]
[/size]Contact your database administrator or refer to SQL Server documentation on how to re-optimize or truncate the database transaction log of content store, reporting db or tempdb. Be sure to back up all databases (if possible) before optimizing or truncating the transaction log.
[/size]
[/size]More Info you can get from [/size]http://www-01.ibm.com/support/docview.wss?uid=swg21345207