OraFAQ Forum: SQL & PL/SQL » to

Home » SQL & PL/SQL » SQL & PL/SQL » to_date error

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

to_date error [message #626002]

Sat, 18 October 2014 11:32

irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan

Senior Member

select *
from emp
where hire_date < to_date('10/25/2014','dd-mon-yyyy')

Give Error?

Report message to a moderator

Re: to_date error [message #626003 is a reply to message #626002]

Sat, 18 October 2014 11:52

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

Your date formats do not match. You need to use either:

to_date('10/25/2014','mm/dd/yyyy')

or:

to_date('25-oct-2014','dd-mon-yyyy')

Report message to a moderator

Re: to_date error [message #626005 is a reply to message #626002]

Sat, 18 October 2014 12:05

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

As Barbara pointed out about the date format model, you can read more about it in documentation here http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements004.htm#SQLRF00212

Alternatively, using ANSI syntax you could also do :

DATE'10/25/2014'

This way you can always use the standard date format regardless of any NLS settings that may be set otherwise.

Also, a good information by Ed Stevens about the order of precedence of NLS DATE settings http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

[Updated on: Sat, 18 October 2014 12:15]

Report message to a moderator

Re: to_date error [message #626016 is a reply to message #626005]

Sun, 19 October 2014 05:40

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Lalit Kumar B

Alternatively, using ANSI syntax you could also do :
DATE'10/25/2014'
This way you can always use the standard date format regardless of any NLS settings that may be set otherwise.

Maybe I don't understand what you are saying, but - what you suggest is NOT ANSI standard.

Datetime literals says:
Oracle

The ANSI date literal contains no time portion, and must be specified in the format 'YYYY-MM-DD'

which means that it should, actually, be

DATE '2014-10-25'

Report message to a moderator

Re: to_date error [message #626020 is a reply to message #626016]

Sun, 19 October 2014 07:39

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Aah, thanks LF for pointing it out. It was a typo error. Copy pasted the literal.

[Updated on: Tue, 21 October 2014 09:34]

Report message to a moderator

Previous Topic:	How to calculate time variation between same day times.
Next Topic:	How to Find Duplicate Groups (group of records) in Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 16:49:51 CDT 2024