CODEWITHSUNDEEP
sqloracle-databaseora-01861
ahmad
ahmad

Reputation: 671

define date format for database in oracle

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?

Upvotes: 0

Views: 1767

Answers (3)

Brian
Brian

Reputation: 9

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');

Upvotes: 0

Stephanie Page
Stephanie Page

Reputation: 3893

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.

Upvotes: 6

Gabriel Magana
Gabriel Magana

Reputation: 4536

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

Upvotes: 1

Related Questions