Inserting date into oracle table

Reply
Frequent Contributor
Posts: 87

Inserting date into oracle table

libname sam_1 "path";

libname sam_1 oracle user = xxxx 
                      password = 'xxxx' 
                      path = 'xxxx';

data office;
input emp dateofjoin asofdate;
datalines;
123 01-nov-2011 15-11-2013
145 27-dec-2012 15-11-2013
run;

proc sql;
    create table sample
as select * from  sam_1.OFFICE ;
quit;


proc sql;
    insert into sam_1.OFFICE values (123,'01-nov-2013','15-nov-2013') ;
insert into sam_1.OFFICE values (123,'12-dec-2012','15-nov-2013') ;
quit;

here i need to insert one dateof join and system date i am unable to insert i an getting output as

output data

emp dateofjoin asofdate

123 01JAN1960:05:27:43 01JAN1960:05:27:43

123 01JAN1960:05:27:43 01JAN1960:05:27:43

how can i update these date values in to oracle

Respected Advisor
Posts: 4,173

Re: Inserting date into oracle table

All dates in Oracle are stored as datetime values.

I'm actually quite amazed that inserting a string like '12-dec-2012' without indication that it represents a date doesn't throw an error when trying to insert into a date column in Oracle.

I can't test it but would assume that if you use an expression like '12-dec-2012'd then the SAS Access engine for Oracle will translate it to an adequate Oracle "to_date()" expression before sending the SQL to Oracle for execution.

Use below option in your code as it will show you in the log what SQL actually gets sent to and executed on the Oracle side.

OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;

Ask a Question
Discussion stats
  • 1 reply
  • 224 views
  • 0 likes
  • 2 in conversation