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.
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;
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.
In my experience more recent SQL Server ODBC drivers do a better job to correctly read or write datetime columns.
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!
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.