Datetime field overflow error

[Sathish_jammy]

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!

[Kurt_Bremser]

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

[Sathish_jammy]

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;

[art297 · Accepted solution]

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

[r_behata]

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 .

[Sathish_jammy]

My SQL Server version is 2014.