help formatting datetime for Oracle pass-through query

[DanD999]

I copied the datetime out of the table but I get an error. The format is

CREAT_DTTM num format=DATETIME24.3 informat=DATETIME24.3 label='CREAT_DTTM',

and hsc.CREAT_DTTM >= '25MAR2014:14:04:41.652'
and hsc.CREAT_DTTM <= '25MAR2014:14:15:46.424'

ERROR: ORACLE execute error: ORA-01849: hour must be between 1 and 12.

[andreas_lds]

It seems that oracle expects time in am/pm notation, the format DATEAMPM30.3 should display the datetime in the expected format.

[DanD999]

If you're suggesting to change the format of the column to DATEAMPM30.3 that is not possible. I have no control over the format that was chosen. Thanks.

[Patrick · Accepted solution]

Are you passing this values within implicit or explicit SQL code. 

If it's withing implicit SAS SQL code (=not within a connect to ... bit) then you need to tell SAS that it needs to interpret the string as a datetime value by adding dt after the quote.

and hsc.CREAT_DTTM >= '25MAR2014:14:04:41.652'dt
and hsc.CREAT_DTTM <= '25MAR2014:14:15:46.424'dt

If above works then you're facing another challenge which is about precision when moving floating point values between environments. Let's address this if above is what makes things work.

If it's pass-through SQL then you need to instruct Oracle to interpret the string as a timestamp value. The To_Timestamp() function allows for this.

[DanD999]

Patrick,

I'm sorry it took me so long to get back to this but it worked. Thanks a lot.