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
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.).
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
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
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;
Please post the SAS log of your Oracle load program.
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 - 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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: