Writing a datatimecolumn to a table in a SQL library: datetime field overflow error

[Filipvdr]

I have a dataset (physical SAS table) on our SAS server. I want to write and store the table in a SQL library.

The dataset contains several columns, including a datetime field. For some reason i'm getting the error below.

When I only insert the first row, it works. So i thought it would be the number of decimals or something...

Error: ERROR: During insert: [SAS][ODBC SQL Server Wire Protocol driver]Datetime field overflow. Error in parameter 2. : [SAS][ODBC SQL
Server Wire Protocol driver]Operation cancelled. Error in parameter 2.

Sample data:

ALARM_ID            ALARM_INITIAL_TIME (format datetime 18.) ALARM_INITIAL_TIME1 (numeric representation of the INITIAL_TIME)

K0820d7daALRM 24DEC18:00:02:09 1861228929.452
K0820dc61ALRM 24DEC18:00:00:02 1861228802.38189
K0820dc62ALRM 24DEC18:00:00:02 1861228802.35839
K0820dc63ALRM 24DEC18:00:00:02 1861228802.42266
K0820dc68ALRM 24DEC18:00:00:17 1861228817
K0820dc69ALRM 24DEC18:00:00:19 1861228819.032
K0820dc6aALRM 24DEC18:00:00:21 1861228820.50327
K0820dc6bALRM 24DEC18:00:00:23 1861228823
K0820dc6cALRM 24DEC18:00:00:23 1861228823

Any ideas?

[PaigeMiller]

Show us the SASLOG for this entire section of code, the entire PROC SQL. Click on the {i} icon and paste that part of the SASLOG into the window that appears.

[kishangabani]

Hello,

you can not apply a standard date format directly against a datetime value, there are some date formats you can prefix with 'DT' which will display a datetime as a date. Unfortunately the MMDDYY format is not one of these.

https://docs.microsoft.com/en-us/sql/t-sql/functions/sysdatetime-transact-sql?view=sql-server-2017

[Filipvdr]

I just want to keep it as a datetime, not to change it in a date in SQL.

Does SQL not allow my format?

[kishangabani]

Hi,

No, because you should apply datetime format  'DATETIME18.'

you applied datetime.

 that is why your datetime column is overflow.

[Filipvdr]

still same error:

data temp;
format alarm_initial_time datetime18.;
set temp_am.query_filip_temp;
run;

data al1bd_01.temp_datetime;
set temp;
run;

[kishangabani]

I have created small example for you. I hope you it goes well.

data test;
	input x datetime.;
	format x datetime18.;
	cards;
25DEC18:00:02:09
;
run;

proc sql;
	insert into test values ('26DEC18:00:02:09'dt);
quit;

proc print data=work.test;
	format x datetime18.;
run;

I also used one PROC SQL. so, it's 18 length of datetime.

Thanks

--