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

[nshTi62]

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?

[Tom]

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.

[nshTi62]

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

Thank you Tom 

[Tom]

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.

[nshTi62]

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

[Tom]

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