Convert date in Data Manager 10.2.1

[tdsrogers]

Hi there,
I have an issue converting a date in Cognos Data Manager.
I am retrieving a date column from a MySQL database using ODBC to a dimension and I'm trying to convert the date from syyyy-mm-dd hh:mi:ss format to yyyymmdd and I am a little stuck.
I am working in the DataStream and am adding a derivation. I have experimented with ToChar, ToDate, RTrim but cannot get it working.
Does anyone have any ideas of how I can do this in the calculation?
Thanks in advance for responses.
tdsrogers

[orbair]

Have you tried cast function?

[tdsrogers]

Hi there,
Thanks for the response but unfortunately the CAST function cannot be used in the data stream in Data Manager.
I could do this in the model but want this to be changed before it gets to that stage.
Thanks.

[MFGF]

Hi there,
I have an issue converting a date in Cognos Data Manager.
I am retrieving a date column from a MySQL database using ODBC to a dimension and I'm trying to convert the date from syyyy-mm-dd hh:mi:ss format to yyyymmdd and I am a little stuck.
I am working in the DataStream and am adding a derivation. I have experimented with ToChar, ToDate, RTrim but cannot get it working.
Does anyone have any ideas of how I can do this in the calculation?
Thanks in advance for responses.
tdsrogers

Hi,

A column of the format yyyymmdd is a numeric value, not a date - correct? If so, you can do the following:

(ToInteger(left(ToChar(YourDate),4)) * 10000) + (ToInteger(Substr(ToChar(YourDate),6,2)) * 100) + ToInteger(Substr(ToChar(YourDate),9,2))

This will return an integer version of the date in the form yyyymmdd

Is this what you need?

MF.