CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle10goracle9i
user1141584
user1141584

Reputation: 619

Oracle Date datatype error

I m little bugged with Oracle

That my SQL below

select * from orders where 
trunc(ordered_date)
between
to_date('01-JAN-12') 
and 
to_date('07-JAN-12')

Ordered_date is DATE datatype

Is it giving the below error.

Error starting at line 1 in command:
select * from orders
where 
trunc(ordered_date)
between 
to_date('01-JAN-12') 
and 
to_date('07-JAN-12')
Error report:
SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 -  "(full) year must be between -4713 and +9999, and not be 0"
*Cause:    Illegal year entered
*Action:   Input year in the specified range

I'm confused what is causing this error in mu code.

Any inputs would be great.

Thanks !!!

Upvotes: 1

Views: 2554

Answers (3)

Rohan
Rohan

Reputation: 2030

Maybe you should convert both the dates, i.e DB date and the 2 input dates, into the same format, like this 


SELECT *
  FROM ORDERS O
 WHERE TO_DATE(TO_CHAR(O.ORDERED_DATE, 'DD-MON-YY'), 'DD-MON-YY') BETWEEN
       TO_DATE('01-JAN-12', 'DD-MON-YY') AND
       TO_DATE('07-JAN-12', 'DD-MON-YY')

Hope it helps

Upvotes: 0

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

If you're really using date literals, you can use this syntax instead:

select * from orders where 
trunc(ordered_date)
between
date '2012-01-01'
and
date '2012-01-07'

Upvotes: 0

user973999
user973999

Reputation:

You have to specified the date format.

Add a parameter to to_date function :

 to_date('01-JAN-12', 'DD-Mon-YY')

Hope it's helps.

Regards.

Upvotes: 1

Related Questions