ORA-01861: literal does not match format string

[Preeti Ramesh]

Hi,
I have a table which has a DATE type field.
When I try and insert a value into it (using java.sql.Timestamp), it gives me error - ORA-01861: literal does not match format string
Then, I tried using the TO_DATE function with a format string as YYYY-MM-DD HH24:MM:SS.MS (My date looks like this 2003-08-28 10:28:25.026) But for that it gives me invalid format..
- Should I change the table field to Timestamp datatype?
Any other way around this??
All help much appreciated.
Thanks,
Preeti

[Nagendra Prasad]

Preeti...
should that not be 'YYYY-MM-DD HH24:MI:SS.MS'
you have used 'YYYY-MM-DD HH24:MM:SS.MS '
check it out.

[Preeti Ramesh]

Hi,
I tried it out..
Query look slike:
......
+ "TO_DATE('" + (new Timestamp(System.currentTimeMillis())) + "','YYYY-MM-DD HH24:MI:SS.MS'), " +
.......

INSERT INTO tss_e110_d3.T_WMMC_FILES_LOG (WMFL_FILE_ID, WMFL_FILE_NAME, WMFL_FORMAT_ID, WMFL_UPLOADED_BY, WMFL_UPLOADED_ON, WMFL_LOCALE_ID, WMFL_STATUS_ID) VALUES ('11', 'uploadFileName.txt', 3, 'TSS_E110_D3_ADMIN' , TO_DATE('2003-08-28 11:01:08.603','YYYY-MM-DD HH24:MI:SS.MS'), 1, 1)
java.sql.SQLException: ORA-01821: date format not recognized
Any idea wht i am doing wrong???
Is it because the millisseconds has 3 digits and not 2?
thanks.

[Nagendra Prasad]

sorry.. did not see it properly.. i tested it against 8.1.7.4.. it doesnot work as date in 8.1/7.4 does not support seconds. It does in Oracle 9.
Here are some support notes:
2. DATETIME DATATYPES
---------------------
Oracle9i has the following datetime datatypes:

o DATE:
Data type which contains the datetime fields YEAR, MONTH, DAY, HOUR,
MINUTE, and SECOND. It does not have fractional seconds and no time zone.

o TIMESTAMP (TS):
Data type which contains the datetime fields YEAR, MONTH, DAY, HOUR,
MINUTE, and SECOND. It has fractional seconds and no time zone.

o TIMESTAMP WITH TIME ZONE (TSTZ):
Data type which contains the datetime fields YEAR, MONTH, DAY, HOUR,
MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional
seconds and an explicit time zone.

o TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ):
Data type which has the storage in the same format as TIMESTAMP. The
difference from TIMESTAMP is:
1. Data is normalized to a database time zone when stored in Oracle
database.
2. When the data is selected by users, the value will be adjusted to the
session time zone.


Where the range of ... is
---------------------- -----------------------------
YEAR -4712..9999 (excluding year 0)
MONTH 01..12
DAY 01..31
HOUR 00..23
MINUTE 00..59
SECOND 00..59.9(N) where "9(N)" indicates the
number of digits specified by
<time fractional seconds
precision>
TIMEZONE_HOUR -12..13
TIMEZONE_MINUTE 00..59

Sample literals of datetime data types:
Data type Literal Example
------------------- ------------------------
DATE DATE '1997-01-31'
TIMESTAMP(2) TIMESTAMP '1997-01-31 09:26:50.10'
TIMESTAMP(0) WITH TIME ZONE TIMESTAMP '1997-01-31 09:26:50+02:00'

I think u might have to create a timestamp column instead.

[Preeti Ramesh]

I tried creating a TIMESTAMP column - no luck.
The *real* problem was that when I formulated the Insert query, the timestamp was converted to a string.
So instead of a statement, I used a PreparedSytatement, and set it as ps.setTimestamp(5, timestampValue);
and it works.. pheeeww
We could use the TO_TIMESTAMP / TO_DATE funbctions I guess, but it makes it really tacky, as they are db specific i believe.
Thanks to all for the very helpful inputs.
cheers - Preeti

[Nagendra Prasad]

thanks for the update. Nice to know that the problem has been solved.
Using the preparedStatement.setTimestamp is the best way forward. agreed.
to_date/to_timestamp are all for those who still have a hangover of Oracle and its wondorous PL/SQL ( me included..! )...

[Padma priya Gururajan]

Hi Preethi,
Will you be able to insert the date from the calendar?
With regards,
Padma priya N.G.

[Jeanne Boyarsky]

Padma,
Note that the original post was from three years ago. It is likely the original poster isn't around here anymore. If you are having a problem, please start a new thread describing it so we can help. You can link to this one if you'd like.