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.
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.
It seems that oracle expects time in am/pm notation, the format DATEAMPM30.3 should display the datetime in the expected format.
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.
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.
Patrick,
I'm sorry it took me so long to get back to this but it worked. Thanks a lot.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.