Relative Date with Time - SQL Server 2000

[system]

Howdy!

Okay, I’ve aksed a similar question before about doing current and previous business day with a time offset. Now I need to figure out how to do Last Full Month, Last Full Week and MTD with the same time offset. What I’ve decided to do is create objects for the starts and ends of these periods and let the report authors build their conditions to their own liking. So what I need to know is, how do I build the formulas for these objects? All times are US Central Time.

<Start of Previous Month, 4:30AM>
<Start of Current Month, 4:30AM> (will double as End of Previous Month)
<Start of Previous Week, 4:30AM>
<Start of Current Week, 4:30AM> (doubles as End of Previous Week)

Suggestions? I know there are probably half a dozen ways at least to do these… any help is greatly appreciated.

Thanks in advance,
Sydney

---

sydneyisle12 (BOB member since 2013-01-10)

[system]

Hi,

This sticky topic can help:

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)

[system]

Yeah, I have that one bookmarked. It’s how I got the current business day and previous business day objects worked out. Sadly, the techniques don’t seem to apply when dealing with week or month, so I’m looking for other options that will work.

UPDATE: Looks like I may have found my way out of this one, too. I took the formulas from this blog: http://blog.sqlauthority.com/2008/08/29/sql-server-few-useful-datetime-functions-to-find-specific-dates/

and replaced the final ‘0’ with ‘04:30:00’. So far, it seems to be working. Waiting for my customer to verify that the numbers match what he gets from other sources.

---

sydneyisle12 (BOB member since 2013-01-10)

[MarkP]

Have a look at the dateadd function - adding 4.5 hours should do the trick.