0

I'm trying to execute the following SQL:

INSERT INTO "x" 
   ("x_id", "requested_function", "user_action", "t_users_id", "date", "wf_process", "details") 
VALUES 
   ('66', 'index', 'view', '1', '2011-02-04 22:14:19', '15', '');

..but i get this error : ORA-01861: literal does not match format string.

This error solved if i add the date format like this:

INSERT INTO "x" 
  ("x_id", "requested_function", "user_action", "t_users_id", "date", "wf_process", "details") 
VALUES 
  ('66', 'index', 'view', '1',to_date('2011-02-04 22:14:19','yyyy-mm-dd hh24:mi:ss'), '15', '');

I don't want to define the date format for each SQL statement, I want this format to be standard for all date fields in the database, without the need to define it,in other word the first SQL statement must run correctly without any errors.

Can I define the date format for oracle database, so no need to define it again with each SQL statement?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
ahmad
  • 671
  • 2
  • 8
  • 20
  • In .NET, I use [`OracleCommandBuilder`](http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommandbuilder.aspx) to avoid those casts. In direct Oracle SQL, I don't know how to avoid it. I would guess you could call some Stored Procedures to define default conversion behaviours. – Uwe Keim Feb 04 '11 at 20:54

3 Answers3

6

That is the wrong way to think about programming. Implicit is bad, explicit is good. If you just toss a string into a data column and hope the default works as you expect, you're very likely to create bugs and performance issues.

But far be it from me to get in the way of getting the answer to the question.

Change the NLS_DATE_FORMAT initialization parameter.

Stephanie Page
  • 3,875
  • 1
  • 18
  • 22
  • 3
    Note, though, that the NLS_DATE_FORMAT you set at the database level will be overridden by the setting on the client. So you can end up with nasty situations where code that does implicit conversions works for some people and not others. – Justin Cave Feb 04 '11 at 21:23
  • Not exactly true OMG. NLS_DATE_FORMAT is session modifiable which means that changes to the value of that parameter only last for the duration of the session that invokes the statement. The value of the parameter does not change for other sessions in the instance. – Stephanie Page Feb 04 '11 at 21:26
  • Justin, if I wanted to do what the OP asks for, he could put the alter session when his app connects, and then not have to specify a date format for THOSE inserts. – Stephanie Page Feb 04 '11 at 21:28
1

That is pretty bad SQL. Two problems: 1) Oracle will not be able to optimize (will have to parse each time you run a query), making database operations slow; and 2) You are open to SQL injection attacks.

It's far better to use parametrized queries with prepared statements, which will solve both issues above and your date formatting problem as well.

EDIT: To directly answer your question, and if you really do not want to change over to prepared statements (you have been warned!), then you can do somethign like this:

INSERT INTO X VALUES (99, TO_DATE('2010/02/04 16:30:00', 'YYYY/MM/DD HH24:MI:SS'));

But again, it's a very bad idea for the reasons stated above

Gabriel Magana
  • 4,338
  • 24
  • 23
0

The SQL92 standard for date and time literals is the way to go here. 

TIMESTAMP 'YYYY-MM-DD HH:MM:SS'
DATE 'YYYY-MM-DD'
TIME 'HH:MM:SS'

An example of using it would be:

INSERT INTO X VALUES (99, TIMESTAMP '2010-02-04 16:30:00');
Brian
  • 9
  • 1
  • That's not how it would work on Oracle. And you can't specify MM for both months and minutes. – Stephanie Page Feb 04 '11 at 21:29
  • This works on Oracle the same way it works on all databases that support SQL92 date and time literals. The top part is just a guide on how to construct such literals. – Brian Feb 04 '11 at 21:34
  • 1
    @Stephanie Page, DATE and TIMESTAMP literals works just fine in Oracle (in 10gr2 at least). I'd even argue that specifying date literals SHOULD be done with DATE instead of to_date(). – Ronnis Feb 04 '11 at 21:48
  • What is the datatype of the column into which you're inserting time? – Stephanie Page Feb 04 '11 at 22:22
  • and using DATE instead of TO_DATE is fantastic if the string you're starting from is already in that format, but if it's mm/dd/yyyy, you'd prefer to do a ton of string gyrations in order to use DATE instead of TO_DATE? – Stephanie Page Feb 04 '11 at 22:24
  • But you're correct that timestamp and date do work. In the case of Oracle I would recommend against the use of TIMESTAMP. As a datatype, TIMESTAMP is very different for DATE and I would find it very confusing to mix the two. – Stephanie Page Feb 04 '11 at 22:26