topic Re: Datetime field overflow error in SAS Programming

Datetime field overflow error

Sathish_jammy — Sun, 10 May 2020 12:08:25 GMT

Hi all,

The date column in the table full_patient_reg is varchar. I converted it to mmddyy10. format.

When I try to export the entire table to SQL Server I receive an error like below.

data proj.full_patient_reg;
set full_patient_reg;
run;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver]Datetime field overflow
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 441255 observations read from the data set WORK.FULL_PATIENT_REG.
WARNING: The data set PROJ.full_patient_reg may be incomplete. When this step was
stopped there were 441254 observations and 20 variables.
ERROR: ROLLBACK issued due to errors for data set PROJ.full_patient_reg.DATA.

Please help me to sort out the error.

Thanks in advance!

Re: Datetime field overflow error

Kurt_Bremser — Sun, 10 May 2020 14:09:45 GMT

Hiw did you convert the value to the mmddyy10. format? Please show this code.

Re: Datetime field overflow error

art297 — Sun, 10 May 2020 14:22:39 GMT

Do one or more of your dates occur before the year 1753?

Take a look at: https://support.sas.com/kb/32/531.html

Art, CEO, AnalystFinder.com

Re: Datetime field overflow error

r_behata — Sun, 10 May 2020 14:38:04 GMT

What is the Version of SQL Server ?

Note that SQL Server 2005 and prior versions do not support Date Only formats, you may have to explicitly convert the SAS Data to datetime prior to loading .

Re: Datetime field overflow error

Sathish_jammy — Sun, 10 May 2020 14:54:55 GMT

The format in the Date variables(varchar) like

1972-10-09 00:00:00
1965-06-21 08:22:19.017

1970-06-23 07:03:00.47

I used the below code to convert into dateformat. All the date variables converted well. but when I export it to Ms SQL it caused an error.

data full_patient_reg;
set Hin.hinai_patient;
Fv_date = input(REGISTRATIONDATE,anydtdte32.);
Birth_Dt = input(DOB,anydtdte32.);
format Fv_date Birth_Dt mmddyy10.;
run;

Re: Datetime field overflow error

Sathish_jammy — Sun, 10 May 2020 14:58:42 GMT

My SQL Server version is 2014.