BookmarkSubscribeRSS Feed
SASACC
Calcite | Level 5
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.
12 REPLIES 12
DBailey
Lapis Lazuli | Level 10
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;
bentleyj1
Quartz | Level 8

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!

Helannivas
Quartz | Level 8

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

Scott_Mitchell
Quartz | Level 8

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

Helannivas
Quartz | Level 8

Scott,

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

Scott_Mitchell
Quartz | Level 8

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

DBailey
Lapis Lazuli | Level 10

How have you defined the data in the flat file?

Helannivas
Quartz | Level 8

Bailey,

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

Helannivas
Quartz | Level 8

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

Thanks to all of you for ur support.

Thanks DBaily for the exp.

bentleyj1
Quartz | Level 8

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.

Peter_C
Rhodochrosite | Level 12

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

SASKiwi
PROC Star

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 7417 views
  • 1 like
  • 7 in conversation