• Print |
• E-mail  |
• Bookmark

When updating an Oracle table with a datetime value, Oracle might return
an error:

  ORACLE execute error: ORA-01850: hour must be between 0 and 23.

This occurs when a datetime value in SAS contains a fraction that, when
rounded, results in the time part being equal to 24:00:00.

Here is an example of a problematic value and the resulting error:

100  libname x oracle user=scott pw=XXXXX path=savigny;
NOTE: Libref X was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: savigny
101  data x.dt24;
102    format dt datetime22.3;
103    dt='10feb2003 23:59:59.500'dt; put dt= datetime.; output;
104  run;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS
tables.
dt=10FEB03:24:00:00
ERROR: ORACLE execute error: ORA-01850: hour must be between 0 and 23.

A circumvention is to use the ROUND() function to adjust the datetime
value:

126  data x.dt24;
127    format dt datetime22.3;
128    dt='10feb2003 23:59:59.500'dt; dt=round(dt,1); put dt= datetime.;
output;

129  run;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS
tables.
dt=11FEB03:00:00:00
NOTE: The data set X.DT24 has 1 observations and 1 variables.


A Technical Support hot fix for Release 8.2 (TS2M0) for this
issue is available at:

http://www.sas.com/techsup/download/hotfix/82_sbcs_prod_list.html#009383

Operating System and Release Information

A fix for Release 8.2 (TS2M0) for this issue is available at: