system
December 10, 2010, 5:20pm
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 (BOB member since 2009-07-20)
system
December 13, 2010, 8:44pm
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 (BOB member since 2009-12-31)
system
December 13, 2010, 8:55pm
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 (BOB member since 2009-07-20)
system
December 13, 2010, 9:08pm
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 (BOB member since 2009-12-31)
system
December 9, 2014, 10:38am
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)
system
December 9, 2014, 3:55pm
6
There are some netezza date functions in the Relative Dates sticky topic here:
As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server.
Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8.
Yesterday
cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime)
Today
cast(convert(char(10),getdate(),23) as datetime)
The following also works for today:
dateadd(dd, datediff(dd,0,getdate()), 0)
Start of Current Month
cast(convert(char(7),getdate(),23)+'-01' as datetime)
End of Current Month
dateadd(d,-1…
Might be worth updating the sticky with any new ones so it all gets kept in one place for posterity?
Debbie
Debbie (BOB member since 2005-03-01)