BookmarkSubscribeRSS Feed
jcheema
Calcite | Level 5

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

6 REPLIES 6
Tom
Super User Tom
Super User

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

'14NOV2011'd

jcheema
Calcite | Level 5

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 .

Tom
Super User Tom
Super User

Did you try a datetime value? 

'14NOV2011:00:00'dt

jcheema
Calcite | Level 5

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

Tom
Super User Tom
Super User

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

Peter_C
Rhodochrosite | Level 12

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 2817 views
  • 0 likes
  • 3 in conversation