- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.).
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
TYPE:- Numeric
FORMAT:- Date9.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Please post the SAS log of your Oracle load program.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.).
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
"*86400 as date_column format=datetime20."
Weird solution but yes a solution
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.