Useful Netezza Date Functions

If you haven't already done so, I highly recommend installing the SQL Extension Toolkit package on your Netezza box. Upon installation, you will have access to many very useful additional functions to make your life easier when writing sql.

Here are some useful date functions I tend to use:

--first of this year
select date_trunc('year', CURRENT_DATE)

--how to get first of the month given a date
select date_trunc('month', current_timestamp)

--how to display monthname-YYYY format - ie December-2012
select trim(to_char(now(),'Month')) || '-' || year(current_date);

-how to display YYYY-MM format - ie 2012-12

select TO_CHAR(current_date,'YYYY-MM');


--truncate timestamp given a date
select date(current_timestamp)--this does not require SQL Extension Toolkit
or
select date_trunc('day', current_timestamp) --requires SQL Extension Toolkit

--select next Monday's date based on today's date
select next_day(DATE(current_timestamp), 'Monday') --if your date is a timestamp and you just want the truncated date for next monday
select next_day(current_date, 'Monday')--if your date is a truncated date

--subtract 1 month from today's date
select date(current_date -  cast('1 month' as interval));

--add 6 days  from today's date

select date(current_date +  cast('6 days' as interval))
or
select  current_date - 7

--if you have a timestamp column and want to subtract say 5 days
select date(yourtimestampcolumn) - 5

--Difference in days between 2 dates (if you have sql exension toolkit installed) 
select EXTRACT (DAY FROM DateCol1 -DateCol2) as difference_in_days
 from tableA
 limit 100;
OR
-- epoch returns number of seconds
select  EXTRACT(epoch FROM DateCol1 -DateCol2)/86400 as difference_in_days
 from TableA
limit 100;

--Difference in hours between 2 dates (epoch returns number of seconds)
select  EXTRACT(epoch FROM DateCol1 -DateCol2)/3600 as difference_in_hours
 from TableA
limit 100;


--for those of you who are used to SQL SERVER's datepart function, you are in luck!
select date_part('day', current_date) as day_of_month, date_part('week', current_date) as week_of_year

--Add 1 hour to  a timestamp
select current_timestamp, current_timestamp +  cast('1 hour' as interval)

--Add 1 hour to a date
select TO_CHAR(datetime(date('2013-02-26') +  cast('1 hour' as interval)),'YYYY-MM-DD HH24:MI:SS')