Wrong Date inserting in oracle table from SAS

[RDS2020]

Hi All,

I am using simple code to insert date field into oracle date field and it is inserting as 01JAN1960.

All other fields are inserging fine other than  Date field which is inserting as 01jan1960 for all rows.

INSERT into oracle_table

Select Date_column from WORK.DATA D ;
QUIT;

Why so? how can we solve this.

Regards,

RDS

[SASKiwi]

Most likely your Oracle date column is in fact a datetime. Check the Oracle table schema to confirm. If so then convert your SAS date into a datetime using the DHMS function before loading into Oracle: DHMS 

[RDS2020]

Hi,

Thanks for reverting. I have checked both Oracle and SAS both are in DATE format. Also i have tried changing Oracle in timestamp thinking it might work but it is still not working.

Regards,

RDS

[SASKiwi]

How is your date variable defined in SAS? It must be defined as a numeric variable with a permanent SAS format assigned to it like DATE9. :

data want;
 format Date_Var date9.;
  Date_Var = '20Jul2020'd;
run;

[RDS2020]

Yes in sas it is defined as
TYPE:- Numeric
FORMAT:- Date9.

[SASKiwi]

Please post the SAS log of your Oracle load program.

[RDS2020]

INSERT into ORA.xyz
30 Select D.*, "Data", "MT" from WORK.DATA D ;
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM| STOP| _DISARM| 2020-07-20T15:01:57,943+10:00| _DISARM| WorkspaceServer| _DISARM| |
_DISARM| | _DISARM| | _DISARM| 23756800| _DISARM| 13| _DISARM| 22| _DISARM| 0| _DISARM| 93884496| _DISARM| | _DISARM| |
_DISARM| | _DISARM| | _DISARM| | _DISARM| | _ENDDISARM
NOTE: 413 rows were inserted into ORA.xyz.

[Patrick · Accepted solution]

An Oracle data type of DATE actually stores DateTime values without fractional seconds. TimeStamp is the same but also stores fractional seconds. 

A SAS Date value is the count of Days since 1/1/1960, a SAS DateTime value is the count of Seconds since 1/1/1960. The values are stored in a SAS Numerical variable and only the format applied will instruct SAS how to treat this numerical value (like how to pass it to Oracle).

I would have thought that SAS gets it right with a Date9. format but from what you describe it doesn't. May be worth raising with SAS TechSupport as that's not behaving like documented here.

You could try the following:

INSERT into oracle_table
  select date_column from
  (
    select Date_column*86400 as date_column format=datetime20. from WORK.DATA D 
  );
QUIT;

Or just a data step before the Insert where you convert the SAS Date value to a SAS DateTime value by multiplying it by the seconds of a day (and also apply format Datetime20.).

[RDS2020]

Adding this to the Date column solves the problem
"*86400 as date_column format=datetime20."

Weird solution but yes a solution

[SASKiwi]

@RDS2020  - Converting your SAS date to a datetime using the DHMS function as in the link I provided would also work. IMO using SAS date functions produces more easily explainable / understandable code.

 select DHMS(Date_column, 0, 0, 0) as date_column format=datetime20.