How to get the day and day number for a specified month

Hi Guys,

I'm trying to set up a report where the user can select a month, and the report will list the days and day numbers for that month. For example, if the user chooses August then the report will show:-

Wednesday 1st

Thursday 2nd

Friday 3rd

... etc

Can anybody help?

TIA.

Create a calendar table with the necessary data (as many years as you want. it will not be a big table even if you use the entire smalldatetime or datetime date range). You can then write a simple query that retrieves the days based on the month. You can also store other attributes like holidays, different calendars (fiscal, yearly, iso), weekends etc.

|||

If you let them choose the month and year, you can build the start date variable and use this script.

Code Snippet

DECLARE @.dateStart datetime

SET @.dateStart = '08/01/2007'

WHILE MONTH(@.dateStart) = 8

BEGIN

print(DATENAME(dw, @.dateStart) + ' ' + CAST(DAY(@.dateStart) AS VARCHAR(2)))

SET @.dateStart = @.dateStart + 1

END

This gives the output

Code Snippet

Wednesday 1

Thursday 2

Friday 3

Saturday 4

Sunday 5

Monday 6

Tuesday 7

Wednesday 8

Thursday 9

Friday 10

Saturday 11

Sunday 12

Monday 13

Tuesday 14

Wednesday 15

Thursday 16

Friday 17

Saturday 18

Sunday 19

Monday 20

Tuesday 21

Wednesday 22

Thursday 23

Friday 24

Saturday 25

Sunday 26

Monday 27

Tuesday 28

Wednesday 29

Thursday 30

Friday 31

sql