asked on Select from Oracle DB all rows going back to 1-Jan four years ago.
Hi
I want to Select from Oracle DB all rows where YEAR portion of invoice = Current Year - 4 years.
I always want the selection to start from January 1 four years ago.
Therefore if the current date is 15-April 2022. I want the selection to start at 1-Jan-2018
I used the following but this starts at 15-Apr- 2018
and to_date(INVOICE_DATE) between to_date(SYSDATE) -4*365.25 and to_date(SYSDATE)
Thanks
Gerald
* Oracle PL/SQLOracle Database
Last Comment
Sean Stuber
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
to_number(sysdate, 'YY')
That's not reliable syntax
If you did want to do extra type conversions then something like this would be more reliable
to_number(to_char(sysdate,'yyyy'),'9999')
But, I think just doing direct date math is more efficient.
That's not reliable syntax
If you did want to do extra type conversions then something like this would be more reliable
to_number(to_char(sysdate,'yyyy'),'9999')
But, I think just doing direct date math is more efficient.
ASKER
suggested exactly what i needed
Agreed, but I don't think that the first SQL answer the question.
... invoice_date between add_months(trunc(sysdate,'yyyy'),-48) and sysdate
Run today, that would window invoices from Feb 14, 2018 and today. (Month - 1) and (Day - 1) need to be subtracted to get to Jan 1.
... invoice_date between add_months(trunc(sysdate,'yyyy'),-48) and sysdate
Run today, that would window invoices from Feb 14, 2018 and today. (Month - 1) and (Day - 1) need to be subtracted to get to Jan 1.
ASKER
Actually I tested it on 2 Oracle DB already.
both go back to 1-Jan 2018.
both go back to 1-Jan 2018.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank again.
I had no idea that trunc(x,'YYYY') returned Jan 1 and I've been in this business a looonnnnnnnnnnggg time!
Thank for cluing me in!
Thank for cluing me in!
You're wlcome!
You can trunc to a lot of different resolutions...
It's an incredibly helpful function.
I wrote an article about it last year exploring some of its utility
https://seanstuber.com/2021/01/30/dates-and-timestamps-the-trunc-function/
You can trunc to a lot of different resolutions...
It's an incredibly helpful function.
I wrote an article about it last year exploring some of its utility
https://seanstuber.com/2021/01/30/dates-and-timestamps-the-trunc-function/
SELECT ...
WHERE invoice_date >= to_date('01-Jan-' || to_char (to_number(sysdate, 'YY')-4))