Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle date formatting question

Posted on 2013-01-25
18
Medium Priority
?
548 Views
Last Modified: 2013-01-28
When attempting to insert data into the database i am getting "ORA-01861: literal does not match format string' error.

Here is my insert statement:

INSERT INTO Table1(Field1)VALUES(to_date('2012-06-12 00:00:00.000', 'DD-MON-YYYY'))

can someone please help me insert the date field without changing either '2012-06-12 00:00:00.000' or the default date format in my database?

There must be a way, using to_char or something
0
Comment
Question by:YZlat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +2
18 Comments
 
LVL 23

Expert Comment

by:David
ID: 38820246
INSERT INTO Table1(Field1)VALUES(to_date('2012-06-12', YYYY-MM-DD')) -- do you really need the inbound precision down to split seconds?

For example, accuracy to minutes would be:
INSERT INTO Table1(Field1)VALUES(to_date('2012-06-12 00:00', YYYY-MM-DD HH24:MI'))
0
 
LVL 35

Author Comment

by:YZlat
ID: 38820293
it needs to be in DD-MON-YYYY format. Is there a function that can convert '2012-06-12 00:00:00.000' to 12-JUN-2012?  I do not need a precision but that's how I get the data
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38820306
I agree with above comments.
see also this article about how to work correctly with dates and conversion from/to strings:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 23

Expert Comment

by:David
ID: 38820318
k, if you are using a DATE (datatype) column, the storage is different from the display.  What I provided was to convert a text string into the DATE storage.

Show us a short example of the data stream please.  I am misunderstanding your VALUES clause.
0
 
LVL 35

Author Comment

by:YZlat
ID: 38820390
can someone just re-write my insert statement?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38820499
as posted above:

INSERT INTO Table1(Field1)VALUES(to_date('2012-06-12 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.SSS'))
or

INSERT INTO Table1(Field1)VALUES(to_date('12-JUN-2012', 'DD-MON-YYYY'))
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38820505
>>or the default date format in my database?

To clarify what others have posted:  Dates in Oracle don't have a format.  They are stored in an internal format.

To get a specific format, you convert the internal format into a string at display or from a string to the internal format on insert.

For what you have posted, you are converting a string into a date with TO_DATE.  If your input string has a different format, just use the correct format mask to match the data.

>>can someone just re-write my insert statement?

It was in http:#a38820246

If you need something different, please explain more about what you are trying to do.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38820587
Are you really trying to insert a string?


INSERT INTO Table1(Field1)VALUES (to_char(timestamp '2012-06-12 00:00:00.000','DD-MON-YYYY'))


if you really want a date out of that timestamp string then, simply insert and the timestamp will downgrade to a DATE type fine


INSERT INTO Table1(Field1)VALUES (timestamp '2012-06-12 00:00:00.000);

or

INSERT INTO Table1(Field1)VALUES (to_timestamp('2012-06-12 00:00:00.000','yyyy-mm-dd hh24:mi:ss.ff');
0
 
LVL 35

Author Comment

by:YZlat
ID: 38826601
OK, so the dates come from a file in the format:

'2012-06-12 00:00:00.000'


I need to insert it into a date field in the database using INSERT statement. There are A LOT of records, so rewriting each one to '12-JUN-2013' or anything else would be a pain.

What I want is construct my insert in such a way that it automatically converts the date to proper format
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38826609
the option has been suggested above already several times, but let me repost it:

INSERT INTO Table1(Field1)VALUES(to_date('2012-06-12 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.SSS'))

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38826615
Don't do anything to format the date, it won't work.
As mentioned before,  dates do NOT have formats.

Only strings have formats.

the main problem here is your string is actually a TIMESTAMP string , not a DATE string.

so, as above,  convert your string into a real TIMESTAMP and let oracle downgrade it to a DATE type


insert into yourtable (yourdatecolumn) values (to_timestamp('2012-06-12 00:00:00.000','yyyy-mm-dd hh24:mi:ss.ff'))
0
 
LVL 35

Author Comment

by:YZlat
ID: 38826625
sdastuber, that is perfect! Exactly what I wanted. Thanks you!
0
 
LVL 35

Author Closing Comment

by:YZlat
ID: 38826628
Thank you!
0
 
LVL 35

Author Comment

by:YZlat
ID: 38826641
AngelIII, you are being very rude. may I point out that this below was NEVER suggested yet.

INSERT INTO Table1(Field1)VALUES(to_date('2012-06-12 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.SSS'))

 So please, stop you are not being helpful at all. Thanks God for real experts with manners who are able to provide efficient solutions without trying to insult anyone
0
 
LVL 35

Author Comment

by:YZlat
ID: 38826662
and by the way,

INSERT INTO Table1(Field1)VALUES(to_date('2012-06-12 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.SSS'))

gives an error
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38826765
YZlat,

sorry for the typo indeed, it should have been .ff and not .sss ...;
which on top does require to_timestamp, to_date would not work indeed.
I stand corrected for that; I had not tested the code (being on mobile)

but I had posted that suggestion before (http://www.experts-exchange.com/Database/Oracle/Q_28009193.html#a38820499)

I also cannot accept your statement that I would have been rude; and surely not very rude.

anyhow, glad you got your solution!
please don't judge that "single" error on this question on my general database/oracle knowledge, though, which would also be rude.

CHeers
a3
0
 
LVL 35

Author Comment

by:YZlat
ID: 38826957
I quote

the option has been suggested above already several times, but let me repost it

Open in new window


the statement above suggests that an option was suggested several times, which it was not. Saying it was implies I am an idiot who cannot read and ignores solutions posted, meanwhile no solutions were posted and by your own admission you have not even bother testing your solution.

Manners my fiend, manners... Absent indeed...
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38827235
actually angeliii had previously posted that same sql statement,  in http:#a38820499

I don't think you should hold it against him that he didn't test it
I didn't test mine either.  For short one-liners like this, most people simply post the syntax they think is correct and wait for feedback.

His mistake was basically the same mistake made by others as well.  confusing to_date with to_timestamp

yes, it's wrong, but fairly minor and certainly no greater error than others made.

let me assure you angeliii has been answering Oracle questions for longer than I have and with a great deal of accuracy.  We all make mistakes, and, having read thousands of his posts I'm sure his intention was not to slight you in anyway.

He may not have helped you on this question; but I hope you'll give him another chance in others.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question