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

· 2 min read

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

Share this post