topic What is the correct syntax for inserting into Oracle date col? in SAS Programming

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

jcheema — Mon, 14 Nov 2011 16:05:20 GMT

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).

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

Tom — Mon, 14 Nov 2011 16:26:57 GMT

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

'14NOV2011'd

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

jcheema — Mon, 14 Nov 2011 16:36:55 GMT

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 .

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

Tom — Mon, 14 Nov 2011 16:51:25 GMT

Did you try a datetime value?

'14NOV2011:00:00'dt

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

jcheema — Mon, 14 Nov 2011 17:07:19 GMT

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

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

Tom — Mon, 14 Nov 2011 17:16:31 GMT

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

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

Peter_C — Tue, 15 Nov 2011 07:56:11 GMT

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