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?
... View more
