3

While writing few queries I needed to return only those rows that have date column set in this year (2017) , that's not my problem I know how to write this query in couple of diffrent ways, but I came across something strange and unexpected for me. Can anyone explain why Oracle db 11.2 is behaving this way? 

select sysdate from dual

returns: 
2017/12/05 09:22:27

select to_date(2017,'YYYY'),trunc(sysdate,'YYYY') from dual
returns :
2017/12/01 00:00:00 2017/01/01 00:00:00

select to_date(2017,'YYYY'),trunc(sysdate,'YYYY') from dual
where trunc(sysdate,'YYYY') = to_date(2017,'YYYY')

no rows returned

Why does to_date(2017,'YYYY') returns 2017/12/01, will it return 2017/01/01 next month? Why does it work that way? I would expect it to always return 2017/01/01 no matter the current month (if month part is indeed changing depending on sysdate).

Dmitriy
  • 5,525
  • 12
  • 25
  • 38
Jakub Dobruchowski
  • 33
  • 1
  • 4
  • 1
    Why this behavior is obvious ? I would excpect function to beheve the same no matter what parameters i put it , and here as per anwser by MT0 for year and month function returns current and for rest returns 1st possible – Jakub Dobruchowski Dec 05 '17 at 09:28
  • @JakubDobruchowski I assume the reason it does not use the current day is so that the function will always return a valid date. Imagine if today was the 31st December and you did `TO_DATE( '02', 'MM' )` and it used the current day of the month then the statement would error as February does not have 31 days. I have never seen a rationale for why current month is used (rather than first month) but I would assume that there is a significant use case for people wanting dates to be of the current month of a specific year and this default behaviour allows for that. – MT0 Dec 05 '17 at 10:00
  • Nice point . I certanly see a strong use case in running someting one per month by just writing to_date(5,'DD') – Jakub Dobruchowski Dec 05 '17 at 10:11
  • Why not simply compare the year instead of comparing a complete date: `where extract(year from some_date) = extract(year from sysdate)` –  Dec 05 '17 at 12:06

1 Answers1

3

In Oracle, TO_DATE will assume that:

  • If you do not specify the year then it is the current year;
  • If you do not specify the month then it is the current month;
  • If you do not specify the day then it is the first day of the month;
  • If you do not specify the hours then it is the midnight hour (0);
  • If you do not specify the minutes then it is 0 minutes past the hour; and
  • If you do not specify the seconds then it is 0 seconds into the minute.

You are specifying only the year (2017) so it will be:

  • Zero minutes and seconds past midnight of the first day of the current month of the year you specify (2017).

If you want the first day of the year then specify the month (and preferably the rest of the date):

select to_date( '201701','YYYYMM'),
       trunc(sysdate,'YYYY')
from   dual
where  trunc(sysdate,'YYYY') = to_date( '201701','YYYYMM' )

Or use a date literal:

select DATE '2017-01-01',
       trunc(sysdate,'YYYY')
from   dual
where  trunc(sysdate,'YYYY') = DATE '2017-01-01'
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Hmm strange i have read couple of sites regarding to_date Oracle funcion and non of them provided info on what to_date will assume. Not even docs . https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm – Jakub Dobruchowski Dec 05 '17 at 09:24
  • @JakubDobruchowski It is an [undocumented "feature"](https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9536628600346884306) of `TO_DATE()`. I too went through the official documentation looking for a reference (and didn't find it) and then had to search quite hard for a secondary reference to back up my experience of how it has always worked across multiple Oracle versions. – MT0 Dec 05 '17 at 09:35