Playing with Date Time in SQL Server

Question

I am building an SSRS report.

In the report, the week always runs from Monday - Sunday.
And I want to find out the START and END dates of prior two weeks.

For example,

If current date = Jan 1, 2011
-- current week = Dec 27, 2010 to Jan 2, 2011
-- previous week = Dec 20, 2010 to Dec 26, 2010

I have tried the following, but seems like it fails when when current day = Sunday

DECLARE @DT DATETIME
DECLARE @Offset INT
DECLARE @CM DATETIME
DECLARE @PM DATETIME
DECLARE @PS DATETIME

--SET @DT = GETDATE()
SET @DT = '11/14/2010' -- Monday
SET @Offset = (DATEPART(WEEKDAY, @DT) - 2) * -1
SET @CM = DATEADD(DAY, @Offset, @DT)
SET @PM = DATEADD(DAY, -7, @CM)
SET @PS = DATEADD(DAY, -1, @CM)

SELECT @Offset AS Offset, @DT AS Date, @CM AS Monday, @PM AS [Previous Monday], @PS AS [Previous Sunday], DATEPART(WK, @PM) AS Week

How can I fix it?

Somewhat unrelated: SQL Server 2008 R2 now has a `DATE` type so you don't need to allocate space for the time. — Matthew, Jan 10 '11 at 23:18

score 2 · Answer 1 · edited Nov 13 '18 at 23:14

2

Add a [Calendar] table to your DB with all the dates you need precalculated. You can then include fields with the day name, number, holiday etc. and simply look up the values you need.

It's much simpler than playing with DATEADD

A useful article on calendar tables: Why should I consider using an auxiliary calendar table?

edited Nov 13 '18 at 23:14

Community

1
1

answered Jan 10 '11 at 22:55

Tony

9,672
3
47
75

+1 for a date table...makes a lot of things easier and faster. – E.J. Brennan Jan 10 '11 at 23:21
+1 dates are relational data: compare answers between functions and date calendar for simplicity http://stackoverflow.com/questions/4647461/hotel-room-rates-for-different-seasons – nate c Jan 11 '11 at 00:45

score 1 · Accepted Answer · answered Jan 10 '11 at 23:16

Not sure if this is the most elegant solution but you could do a case statement with your @offset like this:

SET @offset = CASE WHEN DATEPART(weekday, @today) >= 2
                  THEN -(DATEPART(weekday, @today) - 2)
                  ELSE -(DATEPART(weekday, @today) + 5)
               END

I believe it works for all cases.

Playing with Date Time in SQL Server

2 Answers2