topic Re: time8 is automatically converted to datetime2 in sql server in SAS Programming

time8 is automatically converted to datetime2 in sql server

xiang — Mon, 11 Jan 2016 04:00:24 GMT

sas 9.2, sql server 2008 r2,odbc=sql server native client 10.0

data destlib.tab; --sql server database

set sourcelib.tab; --local sas dataset

run

one column in sourcelib.tab is time8 type with values such as 17:37:33. after running the script above, the column in the sql server database has type datetime2(0) and the value becomes 1960-01-01 17:37:33.

another system

sas 9.4, sql server 2012, odbc=sql server native client 11.0

same script

the column in the sql server database becomes time(7) with the value 17:37:33.0000000

Could someone please tell me why the first scenario happens and where the control is? Thanks

Re: time8 is automatically converted to datetime2 in sql server

PGStats — Mon, 11 Jan 2016 04:29:10 GMT

Look at dataset option DBTYPE, something like data destlib.tab(DBTYPE =(myVar='time(7)'));

Re: time8 is automatically converted to datetime2 in sql server

xiang — Mon, 11 Jan 2016 05:18:20 GMT

Hi PG

Thanks for your reply. Do you know why the first case happened? is it because of some sort of default option?

Thanks

Xiang

Re: time8 is automatically converted to datetime2 in sql server

PGStats — Mon, 11 Jan 2016 16:47:14 GMT

I don't know. To SAS, TIME. and DATETIME. are representations of the same quantity : the number of seconds since 01JAN1960:00:00:00. I guess things just got more sophisticated between versions 9.2 and 9.4.