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
Have you tried cast function?
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.
Quote from: tdsrogers on 04 Jul 2014 10:23:52 AM
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.