aspose file tools*
The moose likes JDBC and the fly likes ORA-01861: literal does not match format string Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ORA-01861: literal does not match format string " Watch "ORA-01861: literal does not match format string " New topic
Author

ORA-01861: literal does not match format string

Preeti Ramesh
Greenhorn

Joined: Jul 24, 2003
Posts: 18
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
Ranch Hand

Joined: Jul 11, 2002
Posts: 219
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.


Best Regards,<br />Nagendra Prasad.
Preeti Ramesh
Greenhorn

Joined: Jul 24, 2003
Posts: 18
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
Ranch Hand

Joined: Jul 11, 2002
Posts: 219
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
Greenhorn

Joined: Jul 24, 2003
Posts: 18
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
Ranch Hand

Joined: Jul 11, 2002
Posts: 219
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
Ranch Hand

Joined: Oct 05, 2006
Posts: 434
Hi Preethi,
Will you be able to insert the date from the calendar?
With regards,
Padma priya N.G.


Padma priya N.G.
Be the change you want to be - Mahatma Gandhi
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ORA-01861: literal does not match format string