system
May 12, 2010, 8:05pm
#1
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.
maineman65 (BOB member since 2007-02-14)
system
May 12, 2010, 8:11pm
#2
Welcome to B:bob:B !
This sticky topic can help you:
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)
MarkP
May 13, 2010, 9:15am
#3
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.
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,
system
May 13, 2010, 4:08pm
#4
Thanks to Marek and Mark; it works great and saved me a lot of tedious trial-and-error.
maineman65 (BOB member since 2007-02-14)
(BOB member since 2003-11-27)