How to get every Monday out of a Date

Question

I need some help to understand a certain line in a code. The code takes the turnovers of every Monday in the year 2010 and at the last line in summarizes all the turnovers to one.

Here is the code:

SELECT
    CASE 
        WHEN GROUPING (DATUM) = 1 THEN 'Gesamtumsatz'
        ELSE CAST (DATUM AS VARCHAR (40))
    END AS MONTAGSDATEN,
    AVG (VERKAUFSWERT * VERKAUFSMENGE) as UMSATZ
FROM Data_Star_Awesome.dbo.VERKAUFSFAKTEN vk
    INNER JOIN DIMDATUM dimD on vk.DATUMID=dimD.DATUMID
WHERE DATEDIFF(dd,0, DATUM)%7=0 
    AND JAHR = 2010
GROUP BY ROLLUP (DATUM)

The problematic line I don't understand is the following:

WHERE DATEDIFF(dd,0, DATUM)%7=0

What I know is that it takes the days out of the date variable but I don't get the %7=0 part. The DATEDIFF function should give back all the days. Are these days saved in the % the placeholder? And how does it get all the Mondays by using the 7=0?

It would be great if someone could help me out.

Thanks a lot :)

The `%` is the mod operator. – Appulus Jan 09 '14 at 17:43 — Appulus, Jan 09 '14 at 17:43

user2989408 · Answer 1 · 2014-01-09T20:02:23.903

Modulo or % operator is the same as in a lot of programming languages. It returns the remainder after the division.

The DATEDIFF function takes two dates and returns the difference in a specified datepart, which in your query is the days represented by dd.

Also 0 as date converts to 1/1/1900 which happens to be a Monday. So your query is calculating the days between 1/1/1900 and the DATUM field in days and if its Mod is 0 then DATUM is Monday.

score 0 · Answer 2 · answered Jan 09 '14 at 19:44

You could simply say:

datename(weekday,<datetime-value>)

Which will return 'Monday', 'Tuesday', 'Wednesday', etc. The problem with this approach is that the returned value is localized. If the SQL server's language is changed, your test for 'Monday' will fail.

This expression will always work:

( @@datefirst + ( datepart(weekday,today) - 1 ) ) % 7

It evaluates to 1-7, where Monday is always 1 and Sunday is always 7, regardless of culture/language settings or the current value of @@datefirst, as set by set datefirst.

We can then convert this into this discriminant function, yielding 1 or 0 to indicate whether or not the date is Monday:

case ( @@datefirst + ( datepart(weekday,today) - 1 ) ) % 7 when 1 then 1 else 0 end

How to get every Monday out of a Date

2 Answers2