Troubleshooting "Datetime field overflow" when dates are recent & dt variable formats are identical

Rodcjones — Tue, 04 Aug 2020 17:01:55 GMT

Within SAS EG, I am appending data from multiple datasets in WORK to various SQL database tables in the PBIDATA library. I am able to append all data without problems except for one. I get this error

NOTE: Appending WORK.SEPSIS to PBIDATA.CEI_SepsisCCG.
NOTE: There were 1 observations read from the data set WORK.SEPSIS.
NOTE: 0 observations added.
NOTE: The data set PBIDATA.CEI_SepsisCCG has . observations and 69 variables.
ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver]Datetime field overflow
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.07 seconds
cpu time 0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.

after submitting this code:

proc append data=sepsis
base=PBIDATA.CEI_SepsisCCG force;run;

I have read up on this error and confirmed none of my datetime variables have dates earlier than the year 2015.

I also confirmed that all datetime variables in the WORK dataset have a format of DATETIME22.3 and that all datetime variables in the PBIDATA dataset also have a format of DATETIME22.3. One difference -and I'm grasping at straws here - is that some INFORMAT values on the WORK dataset are null (rather than matching the format value of DATETIME22.3).

To reitetrate, I have no problems appending other WORK datasets to other PBIDATA datasets, and those tables also include datetime variables. I am wondering if the community has any ideas on what other aspects could be investigated as the cause.

Re: Troubleshooting "Datetime field overflow" when dates are recent & dt variable form

Kurt_Bremser — Tue, 04 Aug 2020 17:18:11 GMT

Keep in mind that Microsoft uses an internal format for times that is different from SAS. In SAS, times are counts of seconds, in MS software, they are fractions of a day (12:00:00 is equal to 0.5).

This might be the cause of your problem.

topic Re: Troubleshooting "Datetime field overflow" when dates are recent & dt variable form in SAS Programming

Troubleshooting "Datetime field overflow" when dates are recent & dt variable formats are identical

Re: Troubleshooting "Datetime field overflow" when dates are recent & dt variable form