Menu
I want to query all products sold in the last 5 years.
It is possible to do it like this:
select * from products
where time between sysdate-1826 and sysdate
But it there also a nicer way instead of calculating all the days and subtract it from sysdate?
631
SELECT * FROM products WHERE date_column >= sysdate - interval '5' year will both give you all the rows from the last 5 years (though you would generally want to add a TRUNC to remove the time portion unless you really care whether a row was created on Feb 8, 2007 in the morning or in the afternoon).
If today's date is 2/2/2012, you’ll see items for the last quarter of 2011. Year ( [SalesDate])*4+DatePart ("q", [SalesDate]) = Year (Date ())*4+DatePart ("q",Date ())+1
This is indirectly addressed by @JustinCave's answer, but 1826 days does not consistently equal 5 years (a 5 year span can contain 1 or 2 leap years). That's why it's better to use operators that deal in whole months or years (which are consistent). SELECT * FROM products WHERE date_column >= add_months ( sysdate, -12*5 )
if you want from 1st Jan 5 years ago DATEADD (year, DATEDIFF (year, 0, GETDATE ()) - 5, 0) if you want 5 years ago today DATEADD (YEAR, -5, DATEADD (day, DATEDIFF (day, 0, GETDATE ()), 0)) Add some details... Tab to the formatting toolbar with Alt/Option + F10. If inside toolbar, press ESC to return to editor.
SELECT *
FROM products
WHERE date_column >= add_months( sysdate, -12*5 )
or
SELECT *
FROM products
WHERE date_column >= sysdate - interval '5' year
will both give you all the rows from the last 5 years (though you would generally want to add a TRUNC to remove the time portion unless you really care whether a row was created on Feb 8, 2007 in the morning or in the afternoon).
172
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
