BusinessObjects Board

Some Date functions for Netezza

building semantic layer
#1

Thought this might be helpful for Netezza Starters – try these for different date calculations:

SELECT
date(current_Date) 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;

mk19 :bangladesh: (BOB member since 2009-07-20)

#2

The syntax is great, Can you add the Date values as well, for example,
add_months(sysdate,-1) will give last month… something like that…

americanmc :hong_kong: (BOB member since 2009-12-31)

#3

Yes, you can get previous months – select add_months(current_date,-1)
from dual;

Please note that Netezza uses current_date as opposed to sysdate in oracle.

mk19 :bangladesh: (BOB member since 2009-07-20)

#4

What I mean is that your syntax below, what is it getting, yesterday, last week??..
so please can you write naem value for all your syntax…thanks

select add_months(current_date,-1) - date_part('day', current_date) 
from dual;

americanmc :hong_kong: (BOB member since 2009-12-31)

#5

Thanks for posting this.

Are there any options for week?

Also found that I couldn’t alternate add_months with add_weeks or add_days,
and getting errors on “Attribute DAY not found” if I left day out of quotation marks, or Function ‘DATE_PART(UNKNOWN, INT4, TIMESTAMP)’ does not exist if I put ‘day’ within quotes.

Bargleshark (BOB member since 2014-12-09)

#6

There are some netezza date functions in the Relative Dates sticky topic here:

Might be worth updating the sticky with any new ones so it all gets kept in one place for posterity?

Debbie

Debbie :uk: (BOB member since 2005-03-01)