How to convert mmddyy10. format to yyyy-mm-dd hh:mm:ss.ss for date variable

[LL5]

I have a sas dataset which needs to be loaded into MS Server Sql. The date fields I have in the SAS dataset are in mmddyy10. format (like 08/31/2020). However, those date columns are defined as datetime in the sql server table, all the date fields in the sql server table are shown as "2020-02-10 00:00:00.000".  As the result, after I load the data from SAS to sql server, all date field become "1960-01-01 06:08:50.000".

Is there a way to convert the date format to  yyyy-mm-dd hh:mm:ss.ss  in SAS before I load the data into SQL server? Or, is there a better alternative way to fix this problem (without altering the column type in the sql server table)?

Any advice would be highly appreciated. 

[Tom · Accepted solution]

In SAS a FORMAT is just instructions on how to display the values. 

If the values work correctly with the MMDDYY format then the values are number of days.  You need to convert the value to number of seconds.  

You can use the DHMS() function to convert from date values (# of days) to datetime values (# of seconds). You will need to pick a time of day, but people normally just use midnight and it looks like that is what you have stored in your external database. Make sure to attach a datetime type format so the values will display in a human recognizable way.

datetime_var = dhms(date_var,0,0,0);
format datetime_var datetime19.;

Note that which format you use to display the datetime values in SAS will not impact the value stored and does not need to match the way that your remote database displays that datetime value.

Note that since both date and datetime values are numbers you could actual re-use the same variable for the new values.

[LL5]

Thanks Tom. I used this approach and now I am seeing "31AUG2020:00:00:00" for the datetime_var. I guess this will work when I loaded into sql server.

data example;
   characterdate = '2020-08-31';
   SasDate = input(characterdate,yymmdd10.);
   datetime_var = dhms(Sasdate,0,0,0);
   format datetime_var datetime20.;

run;

[mkeintz]

You need to change values.  The underlying date value is the number of days after (or before) 01jan1960.   Such values are appropriately formatted for display by such formats as  date9., yymmdd8., mmddyy8., etc.

DATETIME values are the number of seconds after or before  01jan1960:00:00:00.  These values are what is appropriate for datetime formats.  Applying datetime formats to numbers which are just number of days after 01jan1960, will assume you know what you are doing, and interpret the value as number of seconds after 01jan1960:00:00:00.

So first change the value.  Let's say your variable is named mydate.  Then you can issue

     olddate=mydate;   /*Just to save the old value*/
     format olddate date9. ;
     mydate=dhms(mydate,0,0,0);

Now the value in mydate conforms to the format you have applied to the corresponding field in your database.