Hi again,

I would like to change

WHERE dbo.IP_ADMISSION.DischDate >= GETDATE()-7 AND dbo.IP_ADMISSION.DischDate < GETDATE()

to capture Monday to Sunday data only irrespective of what day I run the report

Sorry should have been clearer, the end user would like to data from the previous week Monday to Sunday. The user may be run any day of the following week but alsoway only requires the previous weeks data.

So if I ran the report today i would want the following but always Monday to Sunday of the previous week.

WHERE dbo.IP_ADMISSION.DischDate >= 13/07/15 AND dbo.IP_ADMISSION.DischDate < 20/07/15

if you always want Monday to Sunday of last completed week use

WHERE dbo.IP_ADMISSION.DischDate >=  DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,CASE WHEN DATEDIFF(dd,0,GETDATE()) % 7 < 6 THEN -7 ELSE 0 END) 
AND dbo.IP_ADMISSION.DischDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,CASE WHEN DATEDIFF(dd,0,GETDATE()) % 7 < 6 THEN 0 ELSE 1 END)

if you always want Monday to Sunday of last completed week use

WHERE dbo.IP_ADMISSION.DischDate >=  DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,CASE WHEN DATEDIFF(dd,0,GETDATE()) % 7 < 6 THEN -7 ELSE 0 END) 
AND dbo.IP_ADMISSION.DischDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,CASE WHEN DATEDIFF(dd,0,GETDATE()) % 7 < 6 THEN 0 ELSE 1 END)

 WHERE dbo.IP_ADMISSION.DischDate >=  DATEADD(week, DATEDIFF(week,0,GETDATE())-1,0)  
AND dbo.IP_ADMISSION.DischDate < DATEADD(week, DATEDIFF(week,0,GETDATE()),0)  

• Proposed as answer by Berimi 23 hours 29 minutes ago
• Marked as answer by Eric__ZhangMicrosoft contingent staff, Moderator 23 hours 0 minutes ago

--calculate the weeks has been past since 0(1900-01-01)
--e.g. today is 20150723, so the below query would get 6029 weeks
SELECT DATEDIFF(week,0,GETDATE())

--you got current week's Monday
SELECT DATEADD(week,6029,0)

--you got Monday of one week before
SELECT DATEADD(week,6029-1,0)

--So with the below expression, you got any week's Monday by speicifying -1-2 etc
SELECT DATEADD(week, DATEDIFF(week,0,GETDATE())-1,0) 

--You can extend the expression to get Last Month,Last year and so on

if you always want Monday to Sunday of last completed week use

WHERE dbo.IP_ADMISSION.DischDate >=  DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,CASE WHEN DATEDIFF(dd,0,GETDATE()) % 7 < 6 THEN -7 ELSE 0 END) 
AND dbo.IP_ADMISSION.DischDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,CASE WHEN DATEDIFF(dd,0,GETDATE()) % 7 < 6 THEN 0 ELSE 1 END)

if you always want Monday to Sunday of last completed week use

WHERE dbo.IP_ADMISSION.DischDate >=  DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,CASE WHEN DATEDIFF(dd,0,GETDATE()) % 7 < 6 THEN -7 ELSE 0 END) 
AND dbo.IP_ADMISSION.DischDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,CASE WHEN DATEDIFF(dd,0,GETDATE()) % 7 < 6 THEN 0 ELSE 1 END)

That is an interesting solution.  Could you explain how it works?

Its based on the base date. while / 7 and * 7 it will default to Monday of the current week. then apply proper offset to get Sun and Mon  of current week (if week completed ie today is Sun) or last week

See similar logic used here

And because also 0 represents 1/1/1900 which is the fist day of the week ie Monday.

Important to know.

if you always want Monday to Sunday of last completed week use

WHERE dbo.IP_ADMISSION.DischDate >=  DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,CASE WHEN DATEDIFF(dd,0,GETDATE()) % 7 < 6 THEN -7 ELSE 0 END) 
AND dbo.IP_ADMISSION.DischDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,CASE WHEN DATEDIFF(dd,0,GETDATE()) % 7 < 6 THEN 0 ELSE 1 END)

That is an interesting solution.  Could you explain how it works?

Its based on the base date. while / 7 and * 7 it will default to Monday of the current week. then apply proper offset to get Sun and Mon  of current week (if week completed ie today is Sun) or last week

See similar logic used here

And because also 0 represents 1/1/1900 which is the fist day of the week ie Monday.

Important to

Hi JIMBO,

If you're interested in such kind of approach, you can see Jingyang's, which is more elegant and easier understood.

Its based on the base date. while / 7 and * 7 it will default to Monday of the current week.

Its based on the base date. while / 7 and * 7 it will default to Monday of the current week.

Visakh16, I took a look at the integer arithmetic yesterday.  It looks like the [/ 7] portion truncates the decimal portion of the resulting value producing only an integer.  Would it be safe to say that integers represent a whole day whereas the decimal portion represents the time?

JIMBO

The integers represent  WEEKS and the decimal portion represent days.

The integers represent  WEEKS and the decimal portion represent days.

Berimi,

Do you mean that 7 represents 7 days in a week or literally 7 weeks?  If it is the latter, then I am still unconvinced.  I used the query below to test out the former concept which produces the Monday of the current week in both expressions.

SELECT
    DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0)
    ,DATEADD(WEEK, DATEDIFF(DAY, 0, GETDATE()) / 7, 0)

In the first column, the date part is specified as DAY.  The second column has the date part specified as WEEK.  From the expression for column 1, it appears that 7 represents days in a week.  If I apply the same concept to the expression for column 2, it produces the same result as column 1.

The integers represent  WEEKS and the decimal portion represent days.

Berimi,

Do you mean that 7 represents 7 days in a week or literally 7 weeks?  If it is the latter, then I am still unconvinced.  I used the query below to test out the former concept which produces the Monday of the current week in both expressions.

SELECT
    DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0)
    ,DATEADD(WEEK, DATEDIFF(DAY, 0, GETDATE()) / 7, 0)

In the first column, the date part is specified as DAY.  The second column has the date part specified as WEEK.  From the expression for column 1, it appears that 7 represents days in a week.  If I apply the same concept to the expression for column 2, it produces the same result as column 1.

We are probably splitting hairs at this point, and I am not sure if integer arithmetic is recommended for date calculations.  However, it would be interesting to understand how this math works.

JIMBO,

7 represents 7 days in a week.

DATEDIFF(DAY, 0, GETDATE()) / 7 represents the numbers of complete weeks. in first an second column

DATEDIFF(DAY, 0, GETDATE()) / 7 * 7 represents whole days . in first column

That's why there is DATEADD(WEEK, ...) in the second column and DATEADD(DAY, ...) in first column.