topic Re: help formatting datetime for Oracle pass-through query in SAS Programming

help formatting datetime for Oracle pass-through query

DanD999 — Tue, 31 Mar 2020 09:54:36 GMT

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.

Re: help formatting datetime for Oracle pass-through query

andreas_lds — Tue, 31 Mar 2020 10:09:54 GMT

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

Re: help formatting datetime for Oracle pass-through query

DanD999 — Tue, 31 Mar 2020 12:21:38 GMT

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.

Re: help formatting datetime for Oracle pass-through query

Patrick — Tue, 31 Mar 2020 21:13:15 GMT

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.

Re: help formatting datetime for Oracle pass-through query

DanD999 — Wed, 29 Apr 2020 15:20:13 GMT

Patrick,

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