0

In Our package we had where clause like below.

 pp.start_date >= nvl(TO_DATE('01-JAN-2018', 'RRRR/MM/DD HH24:MI:SS'), pp.start_date)

It was working perfectly in customer environment. But since format was 'RRRR/MM/DD HH24:MI:SS' so we changed the date like below.

pp.start_date >= nvl(TO_DATE('2018/01/01', 'RRRR/MM/DD HH24:MI:SS'), pp.start_date)

Ideally it should work because we have given proper format. in the first case it is returning 679 rows but in second case it is returning 0 rows. 679 correct number of rows we have.

What is wrong with second NVL command?

Improve this question
4
  • 3
    Technically I don't think RRRR is a valid modifier. RR was intended to round a 2-digit year up or down to get the correct century. If you already have a 4-digit year, then use YYYY. Commented Aug 2, 2021 at 21:12
  • I checked with YYYY also RR but output is coming only when I am using date in DD-MON-YYYY format. Commented Aug 2, 2021 at 21:16
  • 2
    Please, provide reproducible example. What you are talking about is not correct either technically, or logically. If RRRR gives you something it doesn't guarantee that it is correct Commented Aug 2, 2021 at 21:21
  • 2
    I can't see how TO_DATE('01-JAN-2018','RRRR/MM/DD HH24:MI:SS') could possibly be "working perfectly". That gives me 20-JAN-2001 18:00:00. Also, why would you hardcode any TO_DATE expression when you could use a standard date literal? Commented Aug 2, 2021 at 21:48

2 Answers 2

Reset to default
5

The way I see it, both are wrong.

If date value is 2018/01/01, then its format is (I presume) yyyy/mm/dd (could be yyyy/dd/mm as well, can't tell just by looking at it). Where did hh24:mi:ss format come from? You never used it.

Therefore, I'd use date literal (which is always date 'yyyy-mm-dd')(as you aren't interested in time component anyway):

where pp.start_date >= date '2018-01-01'

No NVL function. Why? Because date literal can't be NULL.

You would use NVL if it were about some other column, e.g.

where pp.start_date >= nvl(a.some_date, date '2018-01-01')

which means: if a.some_date column is NULL use date '2018-01-01' instead.

[EDIT] If there is time component, after all, use TO_DATE function with appropriate format mask, e.g.

where pp.start_date >= to_date('2018-01-01 23:09', 'yyyy-mm-dd hh24:mi')
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thanks @Littlefoot. Actually date and time is coming from UI. whatever date customer will set, our query will load data from that date.so date/time automatically coming. if we want to keep time, what we should do?
You're welcome. If there are both date AND time involved, use TO_DATE (see edited answer).
What is logical reason of failing second condition but not first one? I mean second not returning any rows. Asking because, i have to give justification to customer.
What is PP.START_DATE's datatype?
If it is DATE, then you have to compare it to another DATE (either it is a DATE datatype column, DATE literal, SYSDATE function, value returned by the TO_DATE function). As you used TO_DATE, then the only "answer" to your question is that you have to use appropriate format mask along with value you provide to the function. If it is e.g. 2018-01-23, then format mask must be YYYY-MM-DD, can't be MM.DD.YYYY nor RRRR-DD-MM nor DD.MM.YYYY HH24:MI:SS nor anything else but format that exactly matches the value.
|
3

If you do:

SELECT TO_DATE('01-JAN-2018', 'RRRR/MM/DD HH24:MI:SS')
FROM   DUAL;

Then, from the string-to-date conversion rules:

  • RRRR also matches RR which matches 01, so the year is 2001.
  • MM also matches MON which matches JAN.
  • DD matches 20.
  • HH24 matches 18.
  • So the date generated is 2001-01-20 18:00:00

If you use:

SELECT TO_DATE('2018/01/01', 'RRRR/MM/DD HH24:MI:SS')
FROM   DUAL;

Then :

  • RRRR matches 2018.
  • MM matches 01.
  • DD matches 01.
  • So the date generated is 2018-01-01 00:00:00

sqlfiddle here

You are generating two completely different dates almost 17 years apart.

In the first case it is returning 679 rows but in second case it is returning 0 rows. 679 correct number of rows we have.

In the first case, you are getting 679 rows that are after 2001-01-20T18:00:00.

In the second case, you are getting 0 rows that are after 2018-01-01T00:00:00.

Given that the dates used were 01-JAN-2018 and 2018-01-01, respectively, I would argue that the second is correct and you should be getting 0 rows and not 679 rows.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.