TSQL Program to determine the day(Name) of the first day of each month in the current year

In this post, l just want to show some cool programming skills and hopefully make it useful for any one interested in learning TSQL and use of variables and some date functions. The main purpose is to write a program that will return the day name such as Monday, Sunday etc of the first day of each month of the current year. So here is the approach we are going to take. Define a variable @FirstDayOfMonth to hold a character string long enough for the date format of your choice.  Define a table variable @results that has two columns to hold the month name and  @FirstDayOfMonth values.  For each month of the current year (variable @mo), set @FirstDayOfMonth equal to a constructed character string representing the first day of the month, and insert this @FirstDayOfMonth value into the @results table.  After exiting the “month” loop, select all rows from @results.  Use of proper data types for your variables is a must. I have written this using sql server 2008 R2. I will attach the output image after the program definition.

DECLARE @FirstDayOfMonth VARCHAR(50),  @mo INT,  @counter INT,  @adate VARCHAR(20),  @DayOfWeek int

DECLARE @DayToCheck int,  @MonthName VARCHAR(20)

DECLARE  @result TABLE(MonthName VARCHAR(50),  FirstDayOfMonth VARCHAR(50))

SET @mo = 1

SET @DayToCheck = 1

WHILE(@mo <= 12)

BEGIN

SET @adate = CAST(@mo AS VARCHAR(10)) + ‘/’ + Cast(@DayToCheck AS VARCHAR(20)) + ‘/’+ CAST(YEAR(GETDATE()) AS VARCHAR(10))

SELECT @DayOfWeek  = DATEPART(WEEKDAY,cast(@adate as DATE))

SELECT @FirstDayOfMonth =

CASE

WHEN @DayOfWeek = 1 THEN ‘SUNDAY’

WHEN @DayOfWeek = 2 THEN ‘MONDAY’

WHEN @DayOfWeek = 3 THEN ‘TUESDAY’

WHEN @DayOfWeek = 4 THEN ‘WEDNESSDAY’

WHEN @DayOfWeek = 5 THEN ‘THURSDAY’

WHEN @DayOfWeek = 6 THEN ‘FRIDAY’

WHEN @DayOfWeek = 7 THEN ‘SATURDAY’

END

SELECT @MonthName =

CASE

WHEN @mo = 1 THEN ‘JANUARY’

WHEN @mo = 2 THEN ‘FEBRUARY’

WHEN @mo = 3 THEN ‘MARCH’

WHEN @mo = 4 THEN ‘APRIL’

WHEN @mo = 5 THEN ‘MAY’

WHEN @mo = 6 THEN ‘JUNE’

WHEN @mo = 7 THEN ‘JULY’

WHEN @mo = 8 THEN ‘AUGUST’

WHEN @mo = 9 THEN ‘SEPTEMBER’

WHEN @mo = 10 THEN ‘OCTOBER’

WHEN @mo = 11 THEN ‘NOVEMBER’

WHEN @mo = 12 THEN ‘DECEMBER’

END

INSERT INTO @result

VALUES (@MonthName,@FirstDayOfMonth)

SET @mo = @mo + 1

END

— end of program, lets see the out come by issuing

SELECT *

FROM @result

MonthName    FirstDayOfMonth
—————————————
JANUARY        SUNDAY
FEBRUARY      WEDNESSDAY
MARCH               THURSDAY
APRIL                 SUNDAY
MAY                    TUESDAY
JUNE                   FRIDAY
JULY                   SUNDAY
AUGUST            WEDNESSDAY
SEPTEMBER     SATURDAY
OCTOBER          MONDAY
NOVEMBER     THURSDAY
DECEMBER       SATURDAY

The result is based on the year the query is executed, the outcome above is for year 2012.

comments and  questitions welcome

Jah bless