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

[Rodcjones]

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.

[Kurt_Bremser]

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.