Oracle SQL query for fetch values by greater than statement on dates

Question

I wrote following simple select query to fetch data, which is greater than the particular given date.

select *
from   XVIIX.emp_tasks
where  TASK_START_DATE > to_Date('30-MAR-18','DD-MM-YYYY');

But the result is not what is expected from that.

Can someone explain me what is the root cause for this behavior?

This cannot work. to_Date('30-MAR-18','DD-MM-YYYY'); MAR cannot be converted into march using MM format. You should have got an error. — ArtBajji, Mar 21 '18 at 11:47
https://stackoverflow.com/questions/10178292/comparing-dates-in-oracle-sql read this question you will understand the problem. — Jay Shankar Gupta, Mar 21 '18 at 11:48

score 3 · Answer 1 · answered Mar 21 '18 at 11:46

3

I think the problem is converting two digit years to four-digit years. It is better to use explicit date literals:

where task_start_date > date '2018-03-30'

answered Mar 21 '18 at 11:46

Gordon Linoff

1,242,037
58
646
786

Thank you for your advice. That solved my problem. – Aravinda Meewalaarachchi Mar 21 '18 at 11:58

score 3 · Answer 2 · answered Mar 21 '18 at 11:49

3

There are two problems with the format model of to_Date('30-MAR-18','DD-MM-YYYY');

The month is expressed as a 3 character month, but the format model for month is MM, which expects a month number, MON is for an abbreviated month name.

YYYY is expecting a 4 digit year, you have supplied two, either supply 4, or change the format model to YY

answered Mar 21 '18 at 11:49

Andrew

26,629
5
63
86

Thank you for your advice. That solved my problem. – Aravinda Meewalaarachchi Mar 21 '18 at 11:58

Oracle SQL query for fetch values by greater than statement on dates

2 Answers2