In this blog post we’ll look at a way to list out all week days of a week a particular date belongs too. This can be even referred to as weekly calendar. This comes handy in reporting. A SQL function to get week days of particular date is given below

First, create the below function to be used as base for the main query

The above function returns a calendar from 19990101 to 25001231. The output from the above function is shown below

1_SQ function to get weekday - weekly calendar

The week days for any date can now be fetched by filtering on week start and end date as shown below (week starts on Sunday)

The output from above statement is shown below

2_SQ function to get weekday - weekly calendar

The output can be modified in calendar format as shown below

The above query pivots the data around days of the week. The result from the above query is shown below

3_SQ function to get weekday - weekly calendar

 

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook