USEFUL NETEZZA DATE FUNCTIONS:

By | February 21, 2011
0 Comment

SELECT date(current_Date) from dual;
–10/28/10 (if current date is Oct 28, 2010)

SELECT (current_timestamp) from dual;

/*
SELECT DATE_PART(‘MONTH’,current_date)from dual;

SELECT current_time, DATE_PART(‘SECOND’,current_time)from dual;

SELECT current_timestamp, DATE_PART(‘SECOND’,current_timestamp) from dual;*/

select add_months(current_date,-1) from dual;

— DATE_TRUNC FUNCTION:
SELECT add_months (date_trunc(‘month’, current_timestamp),-1) from dual;

select add_months (date_trunc(‘month’, current_date),-1) from dual;

–ADD_MONTHS FUNCTION:
select add_months(current_date,-1) – date_part(‘day’, current_date) from dual;

–8/31/10

select add_months(current_date,1) – date_part(‘day’, current_date) from dual;

–10/31/10

–DATE_PART FUNCTION:
select date_part(‘day’, current_date) from dual;
–28

select date_part(‘month’, current_date) from dual;
–10
— date_part will NOT work here:
—SELECT add_months (date_part(‘month’, current_date),-1) from dual;

select add_months(current_date,0) from dual;
–10/28/10

select add_months(current_date,-1) from dual;

select add_months(current_date,-28) from dual;

select add_months(current_date,-1) – date_part(‘month’, current_date) from dual;
–9/18/10

select add_months(current_date,0) – date_part(‘day’, current_date) from dual;
–9/30/10

SELECT date(current_Date) from dual;

–DOESN’T WORK
SELECT date(current_Date,-1) from dual;

—
SELECT (current_Date)-1 from dual;

select add_months(current_date,-1) – date_part(‘day’, current_date) from dual;

select add_months(current_date,1) – date_part(‘day’, current_date)
from dual;

select date_part(‘day’, current_date) from dual;

select date_part(‘month’, current_date) from dual;

select add_months(current_date,0) from dual;

select add_months(current_date,-1) from dual;

select add_months(current_date,-1) – date_part(‘month’, current_date) from dual;

select add_months(current_date,0) – date_part(‘day’, current_date) from dual;

select extract (day from current_date)-7;