0

I am trying to update table where my input Date is of string type

  SELECT FromDate FROM MyTbl where NUM=3;
    *output:*

    FromDate 
------------------------- 
  24-APR-16   

 UPDATE MyTbl SET FLAG='Y' WHERE FromDate=to_date(2016-04-24 00:00:00.0)

   String FromDate= 2016-04-24 00:00:00.0

SQL Error: ORA-01861: literal does not match format string
01861. 00000 -  "literal does not match format string"
*Cause:    Literals in the input must be the same length as literals in
           the format string (with the exception of leading whitespace).  If the
           "FX" modifier has been toggled on, the literal must match exactly,
           with no extra whitespace.
*Action:   Correct the format string to match the literal.
Code Hungry
  • 3,930
  • 22
  • 67
  • 95
  • It looks like you are using Java but it would be helpful to post the code you're using. The `update` statement you posted is missing the quotes around the string value. I'm not sure whether that is because you are dynamically assembling the SQL statement in Java and neglected to add the single quotes or because you are using bind variables in your Java and you just made an error transcribing what the statement would look like with a literal. Additionally, your `to_date` call needs a format mask unless the string happens to match your session's `nls_date_format`. – Justin Cave Apr 24 '16 at 06:11

2 Answers2

0

to_date() expects a string constant and strings are put into single quotes in SQL. You also need to supply a format mask: 

UPDATE MyTbl SET FLAG='Y' 
   WHERE FromDate=to_date('2016-04-24 00:00:00', 'yyyy-mm-dd hh24:mi:ss');

See the manual for more details:

0

Oracle date types only have a resolution to the second. If you want to retain information about milliseconds, then consider using a timestamp and casting to a date:

UPDATE MyTbl
SET FLAG='Y'
WHERE FromDate = CAST(TO_TIMESTAMP('2016-04-24 00:00:00.0', 'YYYY-MM-DD HH24:MI:SS,FF1') AS DATE)

If you really want to retain millisecond precision, then consider changing the FromDate column to timestamp.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360