Inserting DateTime into SQL Server datetime field (via Proc SQL)

mws1985 — Thu, 15 Apr 2021 15:02:12 GMT

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.

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.

Re: Inserting DateTime into SQL Server datetime field (via Proc SQL)

LinusH — Tue, 27 Apr 2021 15:46:54 GMT

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;

Re: Inserting DateTime into SQL Server datetime field (via Proc SQL)

mws1985 — Thu, 15 Jul 2021 14:46:05 GMT

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.

Re: Inserting DateTime into SQL Server datetime field (via Proc SQL)

SASKiwi — Fri, 16 Jul 2021 00:51:56 GMT

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

topic Re: Inserting DateTime into SQL Server datetime field (via Proc SQL) in SAS Data Management

Inserting DateTime into SQL Server datetime field (via Proc SQL)

Re: Inserting DateTime into SQL Server datetime field (via Proc SQL)

Re: Inserting DateTime into SQL Server datetime field (via Proc SQL)

Re: Inserting DateTime into SQL Server datetime field (via Proc SQL)