topic Re: Insert DATETIME Column in SQL Server in SAS Programming

Insert DATETIME Column in SQL Server

rajesh1980 — Mon, 23 Aug 2021 18:51:10 GMT

Hi,

I am trying to insert rows into a SQL Server table from SAS. One of the columns in the table is defined as SMALL DATETIME. Here is the DDL

Create table cccbot.PrgmEnrollments (
ContractAcct VARCHAR(16) null,
ProgramName VARCHAR(35) null,
StartDate date null,
EndDate date null,
Status VARCHAR(30) null,
StatusDate date null,
LoadDate smalldatetime null)
on [PRIMARY] ;

In my SAS code i try this and it is not working

proc sql; insert into IVR_CXBT.PrgmEnrollments select CONTRACT_ACCT as ContractAcct, PROGRAM_PROGRAM as ProgramName, PROGRAM_START_DT as StartDate, PROGRAM_END_DT as EndDate, PROGRAM_STATUS as Status, PROGRAM_STATUS_DT as StatusDate, datetime() as LoadDate format datetime20. from PROGRAM_SUMMARY(obs=2) ; run;

However i get a date time overflow error.

ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver]Datetime field overflow. Error in parameter 7.
ERROR: ROLLBACK issued due to errors for data set IVR_CXBT.PrgmEnrollments.DATA.

I tried a variation of the insert statement with PUT and INPUT statements still no luck.

proc sql; insert into IVR_CXBT.PrgmEnrollments select CONTRACT_ACCT as ContractAcct, PROGRAM_PROGRAM as ProgramName, PROGRAM_START_DT as StartDate, PROGRAM_END_DT as EndDate, PROGRAM_STATUS as Status, PROGRAM_STATUS_DT as StatusDate, input(put(datetime(),datetime20.),datetime20.) as LoadDate from PROGRAM_SUMMARY(obs=2) ; run;

Please help me..

Re: Insert DATETIME Column in SQL Server

Tom — Mon, 23 Aug 2021 18:58:49 GMT

Do you know if by "parameter 7" it means the 7th field?

Try attaching a format to the value so SAS knows you intended as number of seconds instead a generic floating point value.

proc sql;
  insert into IVR_CXBT.PrgmEnrollments  
    select
      CONTRACT_ACCT as ContractAcct
    , PROGRAM_PROGRAM as ProgramName
    , PROGRAM_START_DT as StartDate
    , PROGRAM_END_DT as EndDate
    , PROGRAM_STATUS as Status
    , PROGRAM_STATUS_DT as StatusDate
    , datetime() as LoadDate format=datetime19.
    from PROGRAM_SUMMARY(obs=2)
  ;
run;

All that your current put()/input() sandwich is doing is dropping the fractions of a second. If that is important just use the INT(), FLOOR() or ROUND() function to convert the datatime value into an integer number of seconds.

    , int(datetime()) as LoadDate format=datetime19.

Re: Insert DATETIME Column in SQL Server

rajesh1980 — Mon, 23 Aug 2021 19:00:18 GMT

Hi Tom,

Im getting the same error.

24 proc sql;
25 insert into IVR_CXBT.PrgmEnrollments
26
27 select CONTRACT_ACCT as ContractAcct,
28 PROGRAM_PROGRAM as ProgramName,
29 PROGRAM_START_DT as StartDate,
30 PROGRAM_END_DT as EndDate,
31 PROGRAM_STATUS as Status,
32 PROGRAM_STATUS_DT as StatusDate,
33 datetime() as LoadDate format=datetime19.
34 from PROGRAM_SUMMARY(obs=2)
35 ;
WARNING: Character expression will be truncated when assigned to character column ContractAcct.
WARNING: Character expression will be truncated when assigned to character column Status.
ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver]Datetime field overflow. Error in parameter 7.
ERROR: ROLLBACK issued due to errors for data set IVR_CXBT.PrgmEnrollments.DATA.

Re: Insert DATETIME Column in SQL Server

Tom — Mon, 23 Aug 2021 19:38:58 GMT

In that case the error is probably in how the field is define in the database. Not that I have personal experience, but from other questions I have seen when dealing with how databases define date, time and datetime values they can use other settings.

For example it might be as simple as the database thinking the field is actual a real DATE field and not the DATETIME field you are telling SAS that it is. In that case you could try using the DATE() function, also known as TODAY(), and the DATE9. format instead.

Re: Insert DATETIME Column in SQL Server

rajesh1980 — Mon, 23 Aug 2021 19:44:26 GMT

Hi @Tom ,

I tried and it is still not working I ended up adding a extra column in the input SAS dataset and then referenced it in the INSERT sattement. Not sure why, but it is working, even though im not happy with the solution to the fullest 🙂

/* Merge all PROGRAM_ datasets to one summary dataset*/
data PROGRAM_SUMMARY;
	set PROGRAM_:;
	if PROGRAM_STATUS_DT = '01JAN1900'd then PROGRAM_STATUS_DT = . ;
	PROGRAM_LOAD_DT = put(datetime(),datetime20.);
run;

proc sql;
 	insert into IVR_CXBT.PrgmEnrollments  

		select 	CONTRACT_ACCT as ContractAcct,
				PROGRAM_PROGRAM as ProgramName,
				PROGRAM_START_DT as StartDate,
				PROGRAM_END_DT as EndDate,
				PROGRAM_STATUS as Status,
				PROGRAM_STATUS_DT as StatusDate,
			    input(PROGRAM_LOAD_DT,datetime20.) as LoadDate format datetime20.
		from	PROGRAM_SUMMARY(obs=2)
	;

run;

Re: Insert DATETIME Column in SQL Server

Tom — Mon, 23 Aug 2021 19:56:28 GMT

If that works then the issue probably was the fractional seconds.

Try the version with the INT() function call again.

,int(datetime()) as LoadDate format=datetime19.

Note you don't need include two extra spaces in the DATETIME format, one is enough to get it to print the full four digits of the year and avoid whatever bug causes to use two digits even though there is room for four in 18 characters.

310   data _null_;
311   now=datetime();
312   put now datetime18.
313     / now datetime19.
314     / now datetime20.
315   ;
316   run;

  23AUG21:15:52:01
 23AUG2021:15:52:01
  23AUG2021:15:52:01

Re: Insert DATETIME Column in SQL Server

rajesh1980 — Mon, 23 Aug 2021 21:19:10 GMT

@Tom

No luck. However i noticed that in the SQL Server the datetime is stored as YYYY-MM-DD HH:MM (note that there is space between date and time and not : and there is no seconds as well).

Taking this info i did something like this which is working, better than my first solution. but still i would like to have the datetime called inside the proc sql.

proc format;
	picture myfmt low-high = '%Y-%0m-%0d %0H:%0M' (datatype = datetime);
run;
%let tstamp = %sysfunc(datetime(), myfmt.);

proc sql;
 	insert into IVR_CXBT.PrgmEnrollments  

		select 	CONTRACT_ACCT as ContractAcct,
				PROGRAM_PROGRAM as ProgramName,
				PROGRAM_START_DT as StartDate,
				PROGRAM_END_DT as EndDate,
				PROGRAM_STATUS as Status,
				PROGRAM_STATUS_DT as StatusDate,
			    "&tstamp."dt as LoadDate
		from	PROGRAM_SUMMARY(obs=2)
	;
run;

Re: Insert DATETIME Column in SQL Server

ChrisNZ — Mon, 23 Aug 2021 22:37:35 GMT

Something is weird. Seconds should be accepted according to https://docs.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql?view=sql-server-ver15 but there's something's we're told: 4 bytes can store 4.3 billion values, but the interval given contains 65k days or 5.3 billion seconds. Microsoft shenanigans at play here.

How about:

  case when PROGRAM_LOAD_DT < '01jan1950:00:00'dt then . else int(PROGRAM_LOAD_DT) end as PROGRAM_LOAD_DT format=datetime20.0

or maybe no seconds:

  case when PROGRAM_LOAD_DT < '01jan1950:00:00'dt then . else round(PROGRAM_LOAD_DT,60) end as PROGRAM_LOAD_DT format=datetime20.0

Re: Insert DATETIME Column in SQL Server

ChrisNZ — Tue, 24 Aug 2021 00:22:45 GMT

Actually I see in the examples on the Microsoft page (and in the introduction at the top) that they scrub off seconds, so the numbers make more sense, though by having no seconds means we have 2^32 minutes or 8,000 years, so the limited 179-year interval seems unwarranted. Y2k all over again in 2079....

Re: Insert DATETIME Column in SQL Server

rajesh1980 — Tue, 24 Aug 2021 03:54:33 GMT

@ChrisNZ @Tom

Finally ended up doing this. Im still not sure how this works 🙂

/*setting timestamp format for LoadDate */
proc format;
	picture myfmt low-high = '%Y-%0m-%0d %0H:%0M' (datatype = datetime);
run;


proc sql;
 	insert into IVR_CXBT.PrgmEnrollments  

		select 	CONTRACT_ACCT as ContractAcct,
				PROGRAM_PROGRAM as ProgramName,
				PROGRAM_START_DT as StartDate,
				PROGRAM_END_DT as EndDate,
				PROGRAM_STATUS as Status,
				PROGRAM_STATUS_DT as StatusDate,
			    input(put(datetime(),myfmt.),anydtdtm.) as LoadDate format=myfmt.
		from	PROGRAM_SUMMARY(obs=2)
	;
run;

Re: Insert DATETIME Column in SQL Server

ChrisNZ — Tue, 24 Aug 2021 04:05:00 GMT

You are just rounding by 60 I think.

What happened to using variable PROGRAM_LOAD_DT ?

Re: Insert DATETIME Column in SQL Server

Tom — Tue, 24 Aug 2021 12:03:05 GMT

Not sure why you keep converting your dates into string and then back into numbers by using PUT() and INPUT().

If you want to round to the nearest minute use the ROUND() function.

round(datetime(),60)

Or truncate to the minute

int(datetime()/60)*60

Or use intnx()

intnx('minute',datetime(),0,'b')

Also the format you attach should not matter as long as it is a format designed to operate on datetime values so that SAS knows what type of conversion it should be doing when preparing to transfer the data.

Re: Insert DATETIME Column in SQL Server

rajesh1980 — Thu, 26 Aug 2021 05:35:14 GMT

@ChrisNZ

Apologies for the late reply, was carried away with work.

Tthe PROGRAM_LOAD_DT solution was working earlier as i had mentioned, but i was not happy with it, as it involved creating an extra column in my input dataset, i wanted it more Realtime during the PROC SQL.

Re: Insert DATETIME Column in SQL Server

rajesh1980 — Thu, 26 Aug 2021 05:43:34 GMT

@Tom

ROUND worked like charm..

proc sql;
 	insert into IVR_CXBT.PrgmEnrollments  

		select 	CONTRACT_ACCT as ContractAcct,
				PROGRAM_PROGRAM as ProgramName,
				PROGRAM_START_DT as StartDate,
				PROGRAM_END_DT as EndDate,
				PROGRAM_STATUS as Status,
				PROGRAM_STATUS_DT as StatusDate,
			    round(datetime(),60) as LoadDate
		from	PROGRAM_SUMMARY(obs=2)
	;
run;