CODEWITHSUNDEEP
javasqloracle-databasedate-formatting
Jack
Jack

Reputation: 87

How to convert a Variable in Oracle to date type using TO_DATE

i get the input from java to oracle

custom_date_in IN Table1.custom_date%TYPE,

the input is 2015-07-11 which will stored in custom_date_in

i want insert the input into the table which the column type are Date type

i tried to use the follow sql but fail,

INSERT INTO Table1 (custom_date) SELECT TO_DATE(custom_date_in,'YYYY-MM-DD') FROM TABLE

The following are the error message i get

ORA-01861: literal does not match format string

Upvotes: 2

Views: 2396

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

custom_date_in IN T_MT_APP_DATE.custom_date%TYPE,

The IN parameter is already a DATE data type, why would you convert a date into a date? You need TO_DATE to convert a date literal into date.

So, just assign the date value to the variable:

custom_date := custom_date_in;

Also, you have a missing colon while assigning the value. Keep in mind, in PL/SQL you need to use := instead of =.

Alternatively, you could pass the date value as a string 2015-07-11 i.e. VARCHAR2, and then apply TO_DATE(custom_date_in,'YYYY-MM-DD') or the ANSI date literal DATE '2015-07-11'.

Upvotes: 2

Related Questions