"start of last year " formula

[system]

hi, I am trying to build date formula in Designer to schedule report.

I have “start of this month” (trunc(sysdate,‘MM’))and “start of this year” (trunc(sysdate,‘YYYY’)), how do I get start of last year?

I have tried
add_months(@Select(Dates for Scheduling\Start of this year), -12)

but I get error message say Missing right parenthesis.

Can anyone help?

Thanks a lot.

---

Coolwater (BOB member since 2011-03-30)

[system]

I use:
add_months(trunc(sysdate, ‘YYYY’),-12)
to return 1st Jan last year

---

JoWilson (BOB member since 2006-12-06)

[system]

[Moderator Note: Moved from WebIntelligence XI to Semantic Layer / Universe Designer]

And welcome to B:bob:B!

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Thank you. It works.

---

Coolwater (BOB member since 2011-03-30)

[system]

This can help you in future:

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

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…

---

Marek Chladny (BOB member since 2003-11-27)