topic Re: Date in format DDMONYYYY became 1/1/1960 after inserted into Oracle in SAS Enterprise Guide

Date in format DDMONYYYY became 1/1/1960 after inserted into Oracle

nshTi62 — Tue, 13 Aug 2019 19:47:55 GMT

My date columns are showing in this format DDMONYYYY (ex. 08MAR2018) is showing 01JAN1960:06:00:23 in Oralce table.

I have try to add format=Date9. in the select statement to convert the date but it did not help.

Oracle :

Column: DUE_DATE

Date Type: Date

SAS

Column: Due_Date

Date Type: Date9.

My code:

PROC SQL;

INSERT INTO admr.STAR_ACTION_ITEMS
(
NAME_ID,
DUE_DATE
)
Select
Name_ID,
Due_Date FORMAT=Date9.
FROM WORK.ACTION_ITEMS;
QUIT;

How do you format the date in SAS correctly so that it show the actual date in Oracle table?

Re: Date in format DDMONYYYY became 1/1/1960 after inserted into Oracle

Tom — Tue, 13 Aug 2019 19:55:34 GMT

If you by pass SAS's normal upload method then it can sometimes not realize it needs to convert dates into datetimes to insert into ORACLE. You could convert the value yourself.

INSERT INTO admr.STAR_ACTION_ITEMS (NAME_ID,DUE_DATE)
Select
  Name_ID
, dhms(Due_Date,0,0,0)
FROM WORK.ACTION_ITEMS
;

Or try just using more natural SAS code to add the records.

proc append base=admr.STAR_ACTION_ITEMS data=WORK.ACTION_ITEMS;
run;

And see if SAS is smart enough to transform the values for you.

Re: Date in format DDMONYYYY became 1/1/1960 after inserted into Oracle

nshTi62 — Tue, 13 Aug 2019 20:01:52 GMT

The dhms(Due_Date,0,0,0) worked but the Proc append didn't.

Thank you Tom

Re: Date in format DDMONYYYY became 1/1/1960 after inserted into Oracle

Tom — Tue, 13 Aug 2019 20:09:25 GMT

Did PROC APPEND still load the number of days as if they were number of seconds?

Or did it not execute? Might need the FORCE option if the two datasets don't have the same variables.

Re: Date in format DDMONYYYY became 1/1/1960 after inserted into Oracle

nshTi62 — Tue, 13 Aug 2019 20:11:34 GMT

I did use FORCE in the proc append and the date still went in Oracle as 01JAN1960.

Re: Date in format DDMONYYYY became 1/1/1960 after inserted into Oracle

Tom — Tue, 13 Aug 2019 20:16:30 GMT

Look at this page for how to use dataset options to tell SAS how to convert the variables for you.

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001371624.htm