Netezza SQL show only last year's data

user_12939779 0
Viewed 378

it sounds simple and it should be simple but for some reason I can't seem to make it happen in Netezza... So far I tried:

select * 
from table 
where placed_dt >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0);

and it looked like dateadd function doesn't work on Netezza. So I tried:

select * 
from table 
where placed_dt between (current_date - interval 1 year) and current_date

but still had no luck. Any help would be appreciated!

netezza date sql
2 Answers

If you want the last year from the current date:

where placed_dt >= current_date - interval '1 year'

Note that the single quotes are needed.

and you can include the <= current_date if that is also needed.

If you want the last calendar year, there are various methods, but one is:

where date_trunc('year', placed_dt) = date_trunc('year', current_date) - interval '1 year'
user_11440350

You may try:

SELECT *
FROM yourTable
WHERE
    placed_dt >= ADD_MONTHS(DATE_TRUNC('year', current_timestamp), -12) AND
    placed_at < DATE_TRUNC('year', current_timestamp);

In the above inquality in the WHERE clause, for a current year of 2020, the lower bound represents 2019-01-01 and the upper bound represents 2020-01-01.

user_18632290
Related