2

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.

enter image description here

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

Improve this question
3
  • 1
    Typo: Use to_Date('30-MAR-2018', 'DD-MM-YYYY') Commented Mar 21, 2018 at 11:47
  • 1
    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. Commented Mar 21, 2018 at 11:47
  • stackoverflow.com/questions/10178292/… read this question you will understand the problem. Commented Mar 21, 2018 at 11:48

2 Answers 2

Reset to default
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'
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for your advice. That solved my problem.
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

Improve this answer

1 Comment

Thank you for your advice. That solved my problem.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.