user3838498 user3838498 - 1 year ago 64
SQL Question

Count of weekdays in a given month

I was facing a problem on how to create a calculated column based on a column which contains dates. For example, I have a column which contains dates starting from July.

The DAY needs to be calculated as described in the picture using SQL server.

So basically can a column be created based on an existing column containing a lot of dates? I need this to be dynamic.

WeekDay Date Day
Friday 3-Jan-14 1
Monday 6-Jan-14 2
Tuesday 7-Jan-14 3
Wednesday 8-Jan-14 4
Thursday 9-Jan-14 5
Friday 10-Jan-14 6
Monday 13-Jan-14 7
Tuesday 14-Jan-14 8
Wednesday 15-Jan-14 9
Thursday 16-Jan-14 10
Friday 17-Jan-14 11
Monday 20-Jan-14 12
Tuesday 21-Jan-14 13
Wednesday 22-Jan-14 14
Thursday 23-Jan-14 15
Friday 24-Jan-14 16
Monday 27-Jan-14 17
Tuesday 28-Jan-14 18
Wednesday 29-Jan-14 19
Thursday 30-Jan-14 20
Friday 31-Jan-14 21

Answer Source

With table, DateTable with a column Date of type Date, the following query will do what you ask.

    DATENAME(dw, Date) AS WeekDay
FROM DateTable
WHERE DATEPART(dw, Date) NOT IN (1, 7)