BookmarkSubscribeRSS Feed
mws1985
Calcite | Level 5

Hello,

 

I have a question on inserting a datetime, from SAS, into a SQL Server datetime column, using SAS Data Integration Studio.

 

The target SQL Server field is a  'datetime'. When inspecting the target table in SAS, I see the values in the target SQL Server table defined with a format of DATETIME22.3, as shown below.

datetimeSQLServer.png

 

 

On the SAS side, I am defining a source field, that is calculated using the DATETIME() function, with a format of DATETIME22.3

 

When I run an Update transformation to update 'Personnel', the target SQL Server table, I'd like to update the InsertTimeStamp field with the time that the record was updated, by referencing the column that is calculated in SAS using DATETIME(). My proc SQL statement looks like the following (note I've removed the schema name/LIBNAME reference on the Personnel table, the target table).

update Personnel
      set 
         DeptCode= (
            select
               UpdatePrimaryDepartment.DeptID length = 32   
                  format = $32.
                  informat = $32.
                  label = 'DeptID'
            from
               work.UpdatePrimaryDepartment as UpdatePrimaryDepartment
            where
               Personnel.PersonID = UpdatePrimaryDepartment.PersonID
         ),
         InsertTimestamp= (
            select
               UpdatePrimaryDepartment.insert_time length = 8   
                  format = DATETIME22.3
                  informat = DATETIME22.3
            from
               work.UpdatePrimaryDepartment as UpdatePrimaryDepartment
            where
               Personnel.PersonID = UpdatePrimaryDepartment.PersonID
         )
      where
          EXISTS (
            select *
            from
               work.UpdatePrimaryDepartment as UpdatePrimaryDepartment
            where
               UpdatePrimaryDepartment.PersonID = Personnel.PersonID
               and UpdatePrimaryDepartment.CheckDepartment = 'X'
         )
   ;
quit;

Removing the code to set the InsertTimestamp field works perfectly fine. However, whenever I try to set the InsertTimestamp field, I get the following error message from SAS (which is passed from SQL Server):

 

ERROR: CLI execute error: [SAS][ODBC SQL Server Legacy Driver][SQL Server]Conversion failed when converting date and/or time from character string

 

Is there something obvious I am missing with inserting the datetime values?

 

Thanks.

3 REPLIES 3
LinusH
Tourmaline | Level 20

I don't know why this isn't working, but if you activate tracing it may give you some hints.

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;
Data never sleeps
mws1985
Calcite | Level 5

Thanks, Linus. As it turned out, a different driver needed to be installed on our instance of Data Integration Studio for use with SQL Server databases. Once this was done, it worked fine.

SASKiwi
PROC Star

In my experience more recent SQL Server ODBC drivers do a better job to correctly read or write datetime columns. 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1377 views
  • 0 likes
  • 3 in conversation