Commonly used netezza date functions and how to do data manipulations?

How to use date functions in Netezza?

--to select the current date and timestamp use:

select current_date
select current_timestamp 

--to add months and convert date to integer use:

select to_number(to_char(add_months(current_date, -1),'YYYYMMDD'),'99999999') --to get date id

select to_number(to_char(add_months(current_date, -1),'YYYYMM'),'9999999') --to get month id

select date(current_date -  cast('1 month' as interval))

--to convert timestamp to yyyy-mm-dd format:

select TO_CHAR(TO_DATE('2011-11-24 22:42:28','YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD')

select TO_CHAR(current_timestamp,'YYYY-MM-DD')

--to extract year part from the current date

select extract (year from current_date)
select date_trunc('year', current_date)
to_number(to_char(current_date,'YYYY'),'9999')  --integer format

--to extract day and month from the current date

select extract (day from current_date)

--to get first and last day of the year:

select to_date((extract (year from current_date)) || '0101','YYYYMMDD')
select to_date((extract (year from current_date)) || '1231','YYYYMMDD')

--to get first of the month given a date

select date_trunc('month', current_timestamp)

--to get last month end date

select date_trunc('month', current_timestamp)-1

--to add 7 days to current date

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

--to get timestamp in a specific format

select to_timestamp( current_timestamp, 'YYYY-MM-DD HH24:MI:SS')
select to_timestamp( '2014-12-01 23:12:12', 'YYYY-MM-DD HH24:MI:SS')

--to add hours to a date

 select TO_CHAR(date((current_date +  cast('1 hour' as interval))),'YYYY-MM-DD') 

--To calculate difference in days , weeks between two dates

select datediff(week,'2012-01-01','2015-12-31') as numweeks;

select datediff(day,'2009-01-01','2015-12-31') as numdays;

Also check:
http://dwbitechguru.blogspot.ca/2014/09/jave-program-to-add-or-substract-dates.html

For loading and migration of data in  netezza, check:
http://dwbitechguru.blogspot.ca/2014/11/extract-load-migrate-filesdata-from.html

For SQL server date functions check:
http://dwbitechguru.blogspot.ca/2014/11/sql-server-important-date-manipulation.html