CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle12c
Shruti sharma
Shruti sharma

Reputation: 211

Date format issue in Oracle

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?

Upvotes: 0

Views: 192

Answers (2)

MT0
MT0

Reputation: 167832

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.

Upvotes: 3

Littlefoot
Littlefoot

Reputation: 142720

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')

Upvotes: 5

Related Questions