CODEWITHSUNDEEP
sqldatetimepivotcounting
Markus
Markus

Reputation: 9

Get days in a week

There is the table with the list of random days (in datetime format)

          date
-----------------------
1935-24-25 00:00:00.000
1987-56-15 00:00:00.000
1956-08-05 00:00:00.000
1974-57-25 00:00:00.000

Upvotes: 0

Views: 60

Answers (1)

Strawberry
Strawberry

Reputation: 33935

By observation:

If a month begins on a Monday then there are 4 Thursdays, Fridays, Saturdays and Sundays in that month.

If the month has 28 days, then there are also 4 Mondays, Tuesdays, and Wednesdays in it.

If the month has 29 days, then there are 5 Mondays, and 4 Tuesdays and Wednesdays.

If the month has 30 days, then there are 5 Mondays and Tuesdays, and 4 Wednesdays.

Finally,if the month has 31 days, then there are 5 Mondays, Tuesdays, and Wednesdays.

We can substitute 'Monday' above for day 'A', where day 'A' is whatever day of the week the month starts on.

So all we need to know is the day of the week the month began on, and the number of days in the month. So not really any SQL at all.

Upvotes: 3

Related Questions