topic Re: Problem while loading sas date into oracle in SAS Programming

Problem while loading sas date into oracle

SASACC — Thu, 31 Mar 2011 09:31:21 GMT

Hi,

I have a dataset which has one column which has a format ddmmyy .
when I am trying to copy to oracle it is creating a problem.

Please let me know to which format I should change this field so that oracle support.

Please if with any example would be great.

Thanks.

Re: Problem while loading sas date into oracle

DBailey — Thu, 31 Mar 2011 11:46:21 GMT

In our installation, oracle dates show up as sas datetimes. We have to use the dhms function to create a true datetime prior to loading to oracle or the results is strange.

proc sql;
insert into oracle.table(dtvar)
select dhms(datevar,0,0,0) from sas.table;
quit;

Re: Problem while loading sas date into oracle

bentleyj1 — Tue, 11 Jun 2013 22:39:19 GMT

More than two years later, this fixed my problem.. no matter what I was doing the date was coming out 1/1/1960. SAS 9.1.3 (yes, still) and Oracle 11g. Thanks for posting!

Re: Problem while loading sas date into oracle

Helannivas — Wed, 03 Jul 2013 07:17:29 GMT

Hi bentleyj1,

still i am facing that issue.While loading from flat file to oracle database,i am getting the date column as 01-Jan-1960.

How to overcome tis issue.?I am using SAS DI 4.21
Pls help

Re: Problem while loading sas date into oracle

SASKiwi — Wed, 03 Jul 2013 20:15:35 GMT

I suggest you try writing out to the flat file for loading into Oracle, dates in the form dd-mon-yyyy (01-Jan-2013). This, I believe, is one of the date formats that Oracle should handle automatically, assuming it is being loaded into a date column.

Re: Problem while loading sas date into oracle

Scott_Mitchell — Mon, 08 Jul 2013 04:20:25 GMT

Are you using the OLEDB or Oracle client to connect to Oracle?

I worked for a company that refused to purchase the Oracle client and we were forced to use OLEDB instead. It did some really strange things with dates that took quite a bit of work and research to get around. It might be worth investigating this further.

Regards,

Scott

Re: Problem while loading sas date into oracle

Helannivas — Mon, 08 Jul 2013 05:11:54 GMT

Scott,

I am using Oracle client.....Is ther any way to do this?

Re: Problem while loading sas date into oracle

Scott_Mitchell — Mon, 08 Jul 2013 05:28:42 GMT

Can you please provide a sample of the code you are using and the log output?

Re: Problem while loading sas date into oracle

DBailey — Mon, 08 Jul 2013 13:07:39 GMT

How have you defined the data in the flat file?

Re: Problem while loading sas date into oracle

bentleyj1 — Mon, 08 Jul 2013 18:30:43 GMT

I am not using DI studio so cannot comment on your particular issue other than to say perhaps you need to use a code window. The code provided by DBaily on March 31st, 2011 solved my problem.

Re: Problem while loading sas date into oracle

Peter_C — Mon, 08 Jul 2013 19:42:51 GMT

is it possible to alter the default in SAS/ACCESS, like date <--> datetime

Re: Problem while loading sas date into oracle

Helannivas — Tue, 09 Jul 2013 05:09:44 GMT

Bailey,

I hv defined the column as character datatype(not numeric).

Re: Problem while loading sas date into oracle

Helannivas — Tue, 09 Jul 2013 07:41:46 GMT

dhms(date_column,0,0,0) is working fine......

Thanks to all of you for ur support.

Thanks DBaily for the exp.