topic Re: Datetimeoffset Data type not supported? in SAS Programming

Datetimeoffset Data type not supported?

HCL1991 — Thu, 11 Mar 2021 13:34:56 GMT

I'm trying to query a field named requestdate which is of the datetimeoffset data type from a table named maternitypackages inside a sql server database. If I use a libname statement I can succesfully connect to the database and access the maternitypackage table.

libname _tt   oledb provider=SQLNCLI11.1 dsn="pwt9094" schema=dbo properties=('Initial Catalog'=MaternityPackageModule 'Integrated Security'=SSPI) uid="" pwd="" dbmax_text=1024 preserve_tab_names=yes access=readonly prompt=no;

But the resulting maternitypackages table does not contain the requestdate field.

If I use the sql passthrough facility of sas I can access this field if I cast it as datetime:

proc sql;
connect to oledb as mpm
  (provider=SQLNCLI11.1 dsn=pwt9094 schema=dbo 
properties=('Initial Catalog'=MaternityPackageModule 'Integrated Security'=SSPI)
uid="" pwd="" dbmax_text=1024 prompt=no);
create table ss as 
select * from connection to mpm (select cast(requestdate as datetime) as requestdate from maternitypackages);
;quit;

If I use the sql passthrough facility to access this field without casting I get an error: "ERROR: At least one of the columns in this DBMS table has a datatype that is not supported by this engine."

proc sql;
connect to oledb as mpm
  (provider=SQLNCLI11.1 dsn=pwt9094 schema=dbo 
properties=('Initial Catalog'=MaternityPackageModule 'Integrated Security'=SSPI)
uid="" pwd="" dbmax_text=1024 prompt=no);
create table ss as 
select * from connection to mpm (select requestdate from maternitypackages
                                     );
;quit;

Is casting using the sql passthough facility or a view from the database the only way to fix this problem? What is the best practice in this use case?

Re: Datetimeoffset Data type not supported?

JosvanderVelden — Thu, 11 Mar 2021 17:36:42 GMT

Does this not https://support.sas.com/kb/39/188.html help?

Best regards, Jos

Re: Datetimeoffset Data type not supported?

HCL1991 — Thu, 11 Mar 2021 18:32:33 GMT

Hi,

Do you mean that I should use

SQLNCLI10.1

instead of

SQLNCLI11.1

as provider and this issue will be resolved? If that's the case I can contact our systems administrator to install SQLNCLI10.1. Please verify my assumption.

Re: Datetimeoffset Data type not supported?

Tom — Thu, 11 Mar 2021 18:46:04 GMT

SAS only has two types of variables, floating point numbers and fixed length character strings. To allow you to work with datetime values SAS has some formats, informats and functions that treat the number of seconds since 1960 as a datetime value. But they do not have any concept of storing an offset from GMT in addition to the number of seconds. So for this data to even be usable in SAS you will have to transform it in some way. For example you could eliminate the offset by adjusting the value into either GMT or the servers local time. I assume that is what you say worked for you. Or you could translate it into a string that include the datetime value and the offset. Or store the offset and datetime value as two separate variables.

It sounds like the OLEDB driver you are using does not know how to convert that data type for you.

You might try using the DBSASTYPE= dataset option and see if SAS will cast the value to a character string that you can read.