DATA Step, Macro, Functions and more

What is the correct syntax for inserting into Oracle date col?

Reply
Occasional Contributor
Posts: 8

What is the correct syntax for inserting into Oracle date col?

I have the following code :

proc sql;
insert into ora.sas_ora_out( date_col)
  values('14-NOV-11');
quit;

However, I get this error:

ERROR: Value 1 of VALUES clause 1 does not match the data type of the
       corresponding column in the object-item list (in the SELECT
       clause).

Super User
Super User
Posts: 7,054

What is the correct syntax for inserting into Oracle date col?

Since you are running the SQL in SAS try using SAS syntax for specifying a date literal.

'14NOV2011'd

Occasional Contributor
Posts: 8

What is the correct syntax for inserting into Oracle date col?

Thanks Tom.

That will work but I am trying to find out the correct format.

Actual problem is with a DI job I am creating which needs to insert current date into an Oracle date column. I have used date() function but I am getting 01 Jan 1960 .

Super User
Super User
Posts: 7,054

What is the correct syntax for inserting into Oracle date col?

Did you try a datetime value? 

'14NOV2011:00:00'dt

Occasional Contributor
Posts: 8

What is the correct syntax for inserting into Oracle date col?

Yes

'14NOV2011:00:00'dt works -- incidently '14NOV2011'd would not work.

But I want to use date() or datetime() function in the insert clause  / DI job

Super User
Super User
Posts: 7,054

What is the correct syntax for inserting into Oracle date col?

Oracle only has the DATETIME type.  So use datetime() function. Or other functions that generate datetime values like dhms().

Valued Guide
Posts: 2,177

What is the correct syntax for inserting into Oracle date col?

Since a single column is changing I read the request as an UPDATE, rather than INSERT which only adds rows (iirc)

Ask a Question
Discussion stats
  • 6 replies
  • 609 views
  • 0 likes
  • 3 in conversation