BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sathish_jammy
Lapis Lazuli | Level 10

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!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

 

View solution in original post

5 REPLIES 5
Sathish_jammy
Lapis Lazuli | Level 10

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
Opal | Level 21

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
Barite | Level 11

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
Lapis Lazuli | Level 10

My SQL Server version is 2014.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2540 views
  • 2 likes
  • 4 in conversation