I need help coverting Number\ String to datetime

[B555]

I have two fields that I want to put into one in a date format. I have a number that looks like this 20080404 and string that looks like this 201535. I want to combine the two create a data time field that looks like "4/4/2008 20:15 PM" can anyone help me with this. Thanks

 

[B555]

This would be in cognos Impromptu, I didn't originally put that in there.

 

[drlex]

B555,
The standard time handling facilities of Impromptu are woeful; I think there are only two ways you can do this - either edit the SQL that the report presents to the DB or add in a time add function to Impromptu.

For the former, since your number is in the correct format for the number-to-date function, it's is just a call to cdate, and one can parse the time string to the required format for a ctime call. Using fields called "Date" and "Time", that would give you SQL like:
(cdatetime(((cdate(T1."Date"))) + ctime((od_left(T1."Time",2)) || ':' || (substring(T1."Time" from 3 for 2)) || ':' || (od_right(T1."Time"',2)) || '.000'))).
Editing SQL does curtail your ability to modify the report, so it's an extreme option. Use a back-up copy!

For the latter, see the Cognos Knowledgebase document 1001290.1 which gives an example of the steps to add the DATEADD functionality relating to time to Impromptu when using a SQL server DB as source.

HTH,
lex


soi la, soi carré

 

[B555]

Thanks for the response, I'm actually going to try Both.I like the idea of th add in. Thanks for the help. I will let you know B