convert character datatype to date datatype for date field

[former_member321020]

Hi all,

i am connecting to sql server 2005 through universe designer.

the dates in sql database are in the following format

'YYYYMMDD' -> datatype is character.

Now In the universe i would like to convert datatype 'character' to 'date' .

How to achieve this?

[former_member142702 · Accepted solution]

I guess you can use the cast function of the MS SQL server for this.

Regards,

Stratos

[former_member321020 · Accepted solution]

Hi Stratos,

I am not able to see the CAST function in universe ?

[former_member142702 · Accepted solution]

You just have to type it in the definition of your object .

instead of having a definition like:

myTable.myColumn

you use

CAST(myTable.myColumn AS DATE)

Please search in the internet for the correct syntax of the CAST function (or maybe CONVERT can also help).

Regards,

Stratos

Edited by: Efstratios Karaivazoglou on May 6, 2010 11:35 AM

[former_member321020 · Accepted solution]

hi stratos,

i am able to use the cast or convert function for date field successfully. But when i use same object in webi to build a query i am getting following error.

'Arithmetic overflow error converting expression to data type datetime'

The same above error is occuring when i use for list of values in universe.

[former_member142702 · Accepted solution]

Can you please extract the SQL query from your report and run it directly against the database? DO you get the same error then?

It maybe the case that some of the values stored in the column you are applying the cast operator on cannot be casted.

Regards,

Stratos

[Former Member · Accepted solution]

Hi,

When you defined the SQL expression for your object iusing CAST, did you set the object data type to date?

Didier

[former_member321020 · Accepted solution]

Hi Stratos,

Is there any alternative method?

Hi didier,

Yes i declared object data type as date.

Edited by: sheshikanth reddy on May 16, 2010 3:42 PM

[Former Member · Accepted solution]

Check if you are having Null values in the output. I suspect the error is coming when you are casting a Null to date.