Datetime conversion

[ShortyA]

Hi.
I am using CR XI against an Oracle database connecting via the Oracle driver. Using a formula to convert a string datetime value to a real datetime using :

datetime({MyStringDate})

I am experiencing inconsistent behaviour as follows

Field value Formula output Correct value
10/09/2007 10th Sept 2007 9th Oct 2007
03/25/2007 25th Mar 2007 25th Mar 2007

It seems that by default it is assuming mm/dd/yyy UNLESS it finds a value over 12 as in the second record. Has anyone experienced this inconsistent behaviour as I would expect it to either use mm/dd/yyyy OR dd/mm/yyyy but not switch during the report ?

Many thanks!
ShortyA

 

[IanWaterman]

What is the format of your {MyStringDate}.

You could also DTSToDate ("2000/01/13 11:30:15") but you will need a date time string.

Ian

 

[ShortyA]

Hi Ian.
The format of the field is MM/DD/YYYY which is fine in itself but it is a concern that the Datetime conversion sees it as MM/DD for some dates eg 03/25/2007 but thinks the value is DD/MM for others eg 10/09.
ShortyA

 

[IanWaterman]

OK to be safe use a more complex formula, you may need to check my parentheses.

Date (tonumber(right({MyStringDate},4)), tonumber(left({MyStringDate},2)), tonumber(mid({MyStringDate},4,2)))

This formula can then be formatted as UK date format.

Ian