String to Date/time - Cognos Impromptu 7.4

[thierryverstraete]

Dear user community,

I have a database field defined as a string with example content of '06/30/2009 15:15' (quotes not included in database field). My challenge is to perform date caculations using this date converted string in such a way that I can substract another date. I suspect that it may require the use of the to_date function but when I try e.g. to_date(example string, 'mm/dd/yyyy hh:mm') it complains about the month being invalid. The data is pulled from an Oracle 10g database.

Any help appreciated.

 

[drlex]

Thierry,
In similar circumstances, I have had to use the string functions to extract the day, month and year sections of the string, cast them as numbers and then used the "number-to-date" function. It is neither elegant nor clever, but gets the job done.
In your example, assuming that Oracle pads single figures with leading zeros so that the position is fixed.

number-to-date(string-to-number(Substring(DATE,7,4))*10000 + string-to-number(Substring(DATE,1,2))*100 + string-to-number(Substring(DATE,3,2)))

(If there is no padding of single figure dates, then you need to use 'locate' to identify the placing of the '/' separators)

Perhaps your install allows for the more pleasing SQL 'DATESERIAL' command?

soi la, soi carré

 

[thierryverstraete]

drlex,

Will your formula also convert the time as well?

Thanks!

 

[thierryverstraete]

drlex,

I tried out your formula and it threw an ORA-01722 error. According to

http://ora-01722.ora-code.com/

this means that the attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Any further insight?

 

[drlex]

Thierry,
Apologies - typo on the code - the last part "DATE,3,2)))" should be "DATE,4,2)))". To check, just break out each of the three sections of the addition and make sure that they represent the year (*10000), month (*100) and day of the DATE field.

You can use the same principle to extract the hours and minutes, but I don't have an add-hours or add-minutes function in my Impromptu install. If you have DB access, it might be better to get a view put on the Oracle DB that gives you a proper datetime field to read.

soi la, soi carré