CODEWITHSUNDEEP
sqloracle-database
bk_32
bk_32

Reputation: 529

Why is Oracle ADD_MONTHS() returning a different value for sysdate than if I hardcode today's date?

I need to pull all records from 6 months ago up to present.

At first I hardcoded the date for today (Sep 2, 2021) and got the result I expected:

select case_no, closed_dt from table1
where closed_dt >= add_months(TO_DATE('09/02/2021', 'mm/dd/yyyy'), -6)
order by closed_dt

This gave me the results I wanted, a list of records from Mar 2, 2021, up through today:

CASE_NO | CLOSED_DT
-------------------
     94 | 03/02/2021
     57 | 03/03/2021
     29 | 07/26/2021
     04 | 09/02/2021

However, this query will be in an automatic script and so I cannot hardcode the date, I need it to dynamically change to always be 6 months in the past. So I tried to use sysdate, and got this result instead:

select case_no, closed_dt from table1
where closed_dt >= add_months(sysdate, -6)
order by closed_dt

Result:

CASE_NO | CLOSED_DT
-------------------
     57 | 03/03/2021
     29 | 07/26/2021
     04 | 09/02/2021

As you can see, the record from Mar 2 is now not getting returned, even though it should be.

Even more confusingly, when I do

select add_months(sysdate, -6) from dual

It returns 03/02/2021 as expected.

Can anyone explain what is happening, and provide the correct solution to return records from 6 months ago by using sysdate?

Upvotes: 1

Views: 925

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

An Oracle date always has a day and a time component. TO_DATE('09/02/2021', 'mm/dd/yyyy') returns a date of 2 September 2021 at midnight. On the other hand sysdate returns the current day and time (2 September 2021 13:51:00 on my local machine). Assuming the data in your table has a value of 2 March 2021 at midnight, the first query would return the row while the second query would not because of the time.

I'd expect that you want to use the trunc function to set the time component to midnight

where closed_dt >= add_months(trunc(sysdate), -6)

Upvotes: 2

Related Questions