Date functions in Universe Designer

[system]

Are there any date functions in Universe Designer? I’m trying to create a filter for Yesterday based on a DayOfYear (1 through 365) field in a SQL Server 2000 db.
I know I can create a variable on the report side that will do this, but I’d like to have a predefined filter in the universe that can be used in every report.
Thanks,
MaineMan

---

maineman65 (BOB member since 2007-02-14)

[system]

Welcome to B:bob:B!

This sticky topic can help you:

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…

Or at least it can get you started

---

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

[MarkP]

@maineman65

Are there any date functions in Universe Designer? I’m trying to create a filter for Yesterday based on a DayOfYear (1 through 365) field in a SQL Server 2000 db.
I know I can create a variable on the report side that will do this, but I’d like to have a predefined filter in the universe that can be used in every report.
Thanks,
MaineMan

Say you want a Delivered Yesterday predefined filter. Referencing the link that Marek posted, you would get to the following solution

Create your predefined condition as:

Delivery_Date = cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime)

Regards,
Mark

[system]

Thanks to Marek and Mark; it works great and saved me a lot of tedious trial-and-error.
MaineMan

---

maineman65 (BOB member since 2007-02-14)