DATA Step, Macro, Functions and more

time8 is automatically converted to datetime2 in sql server

Reply
New Contributor
Posts: 2

time8 is automatically converted to datetime2 in sql server

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

Respected Advisor
Posts: 4,925

Re: time8 is automatically converted to datetime2 in sql server

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

PG
New Contributor
Posts: 2

Re: time8 is automatically converted to datetime2 in sql server

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

Respected Advisor
Posts: 4,925

Re: time8 is automatically converted to datetime2 in sql server

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.

PG
Ask a Question
Discussion stats
  • 3 replies
  • 219 views
  • 0 likes
  • 2 in conversation