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