Proc SQL inserting dates issue

[itchyeyeballs]

Hi All,

Having some trouble with dates 😞

I'm trying to update a database table but the dates keep getting scrambled.

my dataset has dates in the format IS8601DA10

I created a table using 

PROC SQL;
CREATE TABLE MIWrite.table5
AS SELECT * FROM desktop.table;
QUIT;

this creates datetimes in my database but seems to work as the dateelement is correct so I was happy.

However when I try to insert new values using

Proc SQL;
INSERT INTO MIWrite.Table5
SELECT 	*
FROM Desktop.table;
QUIT;

All the new dates show along the lines of 1959-12-31 23:50:41.000

I have tried to change the formats of the columns in both the database structure and via SAS code but not able to resolve.

I cant figure out why it works when creating but not when updating 😞

Any ideas, database is MS sql Server

[Kurt_Bremser]

Create an intermediary table in the SQL server and insert there, best by using pass-through SQL.

[Ksharp]

Try to format the datetime variables in Desktop.table with datetime32. format.

[itchyeyeballs]

Hi guys,

Thanks for the tips, I tried to play with both but struggled to get it working, ended up using "put" to covert to text format which is not ideal but better than where I was.

I still can't understand why creating the table using the SAS data set worked fine but then inserting from the same data set (after refreshing) fell over, guess its some kind of SAS vs MS interoperability thing.

[SASKiwi]

I do this myself a lot and as long the date variable is defined as a SAS DATETIME with a format of DATETIME22.2 then I've not had any problems. How is your date variable defined in your SAS table? 

[itchyeyeballs]

Hi @SASKiwi,

The initial data source is an xml file, my map file has (as an example - mostly was auto generated)

        <COLUMN name="BIRTHDTE">
            <PATH syntax="XPath">/StaffRecord/Institution/Person/BIRTHDTE</PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>date</DATATYPE>
            <FORMAT width="10">IS8601DA</FORMAT>
            <INFORMAT width="10">IS8601DA</INFORMAT>
        </COLUMN>

Not sure why but running the create table statement using that source produces a date/time output in my sql server database. 

[SASKiwi]

Please run this and post the output. This will describe how your SAS data is defined:

proc contents data = desktop.table;
run;

[itchyeyeballs]

Sample attached

[SASKiwi]

I suggest you convert BIRTHDTE from DATE to DATETIME:

birthdte = dhms(birthdte, 0, 0, 0);
format birthdte datetime 22.2;

 and try inserting that.