SQL SERVER – UDF to Return a Calendar for Any Date for Any Year

22 Comments. Leave new

• 

  Jeff Moden

  February 24, 2008 7:38 am

  Well howdy…

  Everytime I see one of these calendar generators, I have to say… sure, they make an interesting exercise… but what good are they and what have YOU actually used one for?

  Thanks,
  –Jeff Moden

  Reply
• 

  phil Factor

  February 24, 2008 3:33 pm

  Howdy Jeff,

  I don’t know how useful this particular one is, but the one I wrote way back on Simple-talk has been adapted to a couple of live applications. In one case it turned into a Golfing Event diary for an application Robyn and I wrote. It also formed the bedrock for a reporting application where trends over the day of the week and week of the month was important to the business (telephony)

  it is quite handy to have this sort of application in TSQL where you are subsequently going to pin data onto it.

  Reply
• 

  Jan Flodin

  February 27, 2008 1:03 pm

  Thanks a lot. I will use it. One question though. I would like to use “SET DATEFIRST 1” which means Monday will be the first day in the week for me. Where will I typically put the datefirst statement?

  Regards,

  Jan Flodin

  Reply
• 

  Elias Horne

  May 23, 2008 1:58 am

  Hello,

  I tried this particular code and seem to have found an error. I tried using 2007-12-01 00:00:00.000 as a date and it stopped at 12/29/2007. No Sunday 12/30/2007 or Monday 12/31/2007.

  Any ideas what is causing this or what I might be doing wrong?

  Reply
• 

  Mithun Ghosh

  June 27, 2008 10:23 am

  How to retrieve the weekending dates of a particular month and year .
  we need the stored procedures for this work where month and year are passed as parameters.

  e.g : if @month=May

  @year=2008

  Then the weekending dates should return 2-May-2008,

  9-May-2008,16-May-2008,23-May-2008,30-May-2008.

  Reply
• 

  Oliver Kanp(Microsoft)

  June 27, 2008 3:07 pm

  –Solution For Mithun Ghosh

  Function of the millennium

  Create FUNCTION [dbo].[DisplayCurrentWeekDays]
  (@today SMALLDATETIME)
  RETURNS @WeekDateDay TABLE
  (
  Saturday SMALLDATETIME,
  Sunday SMALLDATETIME,
  Monday SMALLDATETIME,
  Tuesday SMALLDATETIME,
  Wednesday SMALLDATETIME,
  Thursday SMALLDATETIME,
  Friday SMALLDATETIME

  )
  AS
  BEGIN
  DECLARE @day INT
  SET @today = CAST(CONVERT(VARCHAR(10), @today, 101) AS SMALLDATETIME)
  SET @day = DATEPART(dw, @today)
  INSERT INTO @WeekDateDay (Saturday,Sunday, Monday,
  Tuesday, Wednesday, Thursday, Friday )
  SELECT
  DATEADD(dd, 0 – @day, @today) Saturday,
  DATEADD(dd, 1 – @day, @today) Sunday,
  DATEADD(dd, 2 – @day, @today) Monday,
  DATEADD(dd, 3 – @day, @today) Tuesday,
  DATEADD(dd, 4 – @day, @today) Wednesday,
  DATEADD(dd, 5 – @day, @today) Thursday,
  DATEADD(dd, 6 – @day, @today) Friday

  RETURN
  END
  ————————————————————

  set ANSI_NULLS ON
  set QUOTED_IDENTIFIER ON
  go

  Create FUNCTION [dbo].[GetSQLcalendar](@WhatDate SMALLDATETIME)
  RETURNS @WeekDateDay TABLE
  (
  Friday SMALLDATETIME
  )
  AS
  BEGIN
  DECLARE @FourWeeksAgo SMALLDATETIME
  SET @FourWeeksAgo = DATEADD(d,-28,@WhatDate)
  DECLARE @ThreeWeeksAgo SMALLDATETIME
  SET @ThreeWeeksAgo = DATEADD(d,-21,@WhatDate)
  DECLARE @TwoWeeksAgo SMALLDATETIME
  SET @TwoWeeksAgo = DATEADD(d,-14,@WhatDate)
  DECLARE @PreviousWeek SMALLDATETIME
  SET @PreviousWeek = DATEADD(d,-7,@WhatDate)
  DECLARE @ThisWeek SMALLDATETIME
  SET @ThisWeek = (@WhatDate)
  DECLARE @NextWeek SMALLDATETIME
  SET @NextWeek = DATEADD(d,7,@WhatDate)
  DECLARE @LastWeek SMALLDATETIME
  SET @LastWeek = DATEADD(d,14,@WhatDate)
  DECLARE @AfterLastWeek SMALLDATETIME
  SET @AfterLastWeek = DATEADD(d,21,@WhatDate)
  DECLARE @TwoAfterLastWeek SMALLDATETIME
  SET @TwoAfterLastWeek = DATEADD(d,28,@WhatDate)
  INSERT INTO @WeekDateDay (Friday)
  SELECT *
  FROM
  (
  SELECT
  CASE MONTH(calendar.Friday)
  WHEN MONTH(@WhatDate)
  THEN calendar.Friday
  ELSE NULL END AS ‘Friday’
  FROM
  (
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@FourWeeksAgo)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@ThreeWeeksAgo)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@TwoWeeksAgo)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@PreviousWeek)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@ThisWeek)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@NextWeek)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@LastWeek)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@AfterLastWeek)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@TwoAfterLastWeek)
  ) calendar
  ) dates
  WHERE dates.friday IS NOT NULL

  RETURN
  END

  ———————————————————

  USE AdventureWorks;
  GO
  DECLARE @today SMALLDATETIME
  SELECT @today = ‘7/4/2008’;
  –SELECT ISNULL(CONVERT(NVARCHAR(20), Saturday, 101), ”) AS ‘Saturday’,
  — ISNULL(CONVERT(NVARCHAR(20), Sunday, 101), ”) AS ‘Sunday’,
  — ISNULL(CONVERT(NVARCHAR(20), Monday, 101), ”) AS ‘Monday’,
  — ISNULL(CONVERT(NVARCHAR(20), Tuesday, 101), ”) AS ‘Tuesday’,
  — ISNULL(CONVERT(NVARCHAR(20), Wednesday, 101), ”) AS ‘Wednesday’,
  — ISNULL(CONVERT(NVARCHAR(20), Thursday, 101), ”) AS ‘Thursday’,
  SELECT ISNULL(CONVERT(NVARCHAR(20), Friday, 101), ”) AS ‘Friday’

  FROM dbo.GetSQLcalendar(@today)
  GO
  ————————————————————————–
  ——————————————–
  set ANSI_NULLS ON
  set QUOTED_IDENTIFIER ON
  go

  Create Procedure proc_weekendingdate
  @WhatDate smalldatetime

  AS
  SET NOCOUNT ON

  SELECT ISNULL(CONVERT(NVARCHAR(20), Friday, 107), ”) AS ‘WeekEndingDate’

  FROM GetSQLcalendar(@WhatDate)

  GO

  exec proc_weekendingdate
  ‘6/27/2008’

  ————————————–
  set ANSI_NULLS ON
  set QUOTED_IDENTIFIER ON
  go

  Create Procedure proc_CurrentWeekDate
  @CurrentDate smalldatetime

  AS
  SET NOCOUNT ON

  –SELECT @today = ‘7/4/2008’;
  SELECT ISNULL(CONVERT(NVARCHAR(20), Saturday,007), ”) AS ‘Saturday’,
  ISNULL(CONVERT(NVARCHAR(20), Sunday, 007), ”) AS ‘Sunday’,
  ISNULL(CONVERT(NVARCHAR(20), Monday, 007), ”) AS ‘Monday’,
  ISNULL(CONVERT(NVARCHAR(20), Tuesday, 007), ”) AS ‘Tuesday’,
  ISNULL(CONVERT(NVARCHAR(20), Wednesday,007), ”) AS ‘Wednesday’,
  ISNULL(CONVERT(NVARCHAR(20), Thursday,007), ”) AS ‘Thursday’,
  ISNULL(CONVERT(NVARCHAR(20), Friday, 007), ”) AS ‘Friday’

  FROM dbo.DisplayCurrentWeekDays(@CurrentDate)
  GO

  exec proc_CurrentWeekDate
  ‘7/3/2008’

  Reply
• 

  Doug

  July 29, 2008 7:24 am

  There is an Error in this Code…try the date ‘8/1/2008’ it drops the ‘8/31/2008’.
  Here is the fix

  set ANSI_NULLS ON
  set QUOTED_IDENTIFIER ON
  go

  ALTER FUNCTION [dbo].[GetSQLcalendar](@WhatDate SMALLDATETIME)
  RETURNS @WeekDateDay TABLE
  (
  Sunday SMALLDATETIME,
  Monday SMALLDATETIME,
  Tuesday SMALLDATETIME,
  Wednesday SMALLDATETIME,
  Thursday SMALLDATETIME,
  Friday SMALLDATETIME,
  Saturday SMALLDATETIME
  )
  AS
  BEGIN
  /*
  Purpose: To return a calendar fOR whatever MONTH you want, driven by the date you pick AS a variable.
  By: Dan Golden
  On: 2/20/2008
  */
  DECLARE @FourWeeksAgo SMALLDATETIME
  SET @FourWeeksAgo = DATEADD(d,-28,@WhatDate)
  DECLARE @ThreeWeeksAgo SMALLDATETIME
  SET @ThreeWeeksAgo = DATEADD(d,-21,@WhatDate)
  DECLARE @TwoWeeksAgo SMALLDATETIME
  SET @TwoWeeksAgo = DATEADD(d,-14,@WhatDate)
  DECLARE @PreviousWeek SMALLDATETIME
  SET @PreviousWeek = DATEADD(d,-7,@WhatDate)
  DECLARE @ThisWeek SMALLDATETIME
  SET @ThisWeek = (@WhatDate)
  DECLARE @NextWeek SMALLDATETIME
  SET @NextWeek = DATEADD(d,7,@WhatDate)
  DECLARE @LastWeek SMALLDATETIME
  SET @LastWeek = DATEADD(d,14,@WhatDate)
  DECLARE @AfterLastWeek SMALLDATETIME
  SET @AfterLastWeek = DATEADD(d,21,@WhatDate)
  DECLARE @TwoAfterLastWeek SMALLDATETIME
  SET @TwoAfterLastWeek = DATEADD(d,28,@WhatDate)
  DECLARE @ThreeAfterLastWeek SMALLDATETIME
  SET @ThreeAfterLastWeek = DATEADD(d,35,@WhatDate)
  INSERT INTO @WeekDateDay (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)
  SELECT *
  FROM
  (
  SELECT CASE MONTH(calendar.Sunday)
  WHEN MONTH(@WhatDate)
  THEN calendar.Sunday
  ELSE NULL END AS ‘Sunday’,
  CASE MONTH(calendar.Monday)
  WHEN MONTH(@WhatDate)
  THEN calendar.Monday
  ELSE NULL END AS ‘Monday’,
  CASE MONTH(calendar.Tuesday)
  WHEN MONTH(@WhatDate)
  THEN calendar.Tuesday
  ELSE NULL END AS ‘Tuesday’,
  CASE MONTH(calendar.Wednesday)
  WHEN MONTH(@WhatDate)
  THEN calendar.Wednesday
  ELSE NULL END AS ‘Wednesday’,
  CASE MONTH(calendar.Thursday)
  WHEN MONTH(@WhatDate)
  THEN calendar.Thursday
  ELSE NULL END AS ‘Thursday’,
  CASE MONTH(calendar.Friday)
  WHEN MONTH(@WhatDate)
  THEN calendar.Friday
  ELSE NULL END AS ‘Friday’,
  CASE MONTH(calendar.Saturday)
  WHEN MONTH(@WhatDate)
  THEN calendar.Saturday
  ELSE NULL END AS ‘Saturday’
  FROM
  (
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@FourWeeksAgo)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@ThreeWeeksAgo)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@TwoWeeksAgo)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@PreviousWeek)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@ThisWeek)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@NextWeek)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@LastWeek)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@AfterLastWeek)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@TwoAfterLastWeek)
  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@ThreeAfterLastWeek)
  ) calendar
  ) dates
  WHERE dates.sunday IS NOT NULL
  OR dates.monday IS NOT NULL
  OR dates.tuesday IS NOT NULL
  OR dates.wednesday IS NOT NULL
  OR dates.thursday IS NOT NULL
  OR dates.friday IS NOT NULL
  OR dates.saturday IS NOT NULL
  RETURN
  END

  BTW I Added

  DECLARE @ThreeAfterLastWeek SMALLDATETIME
  SET @ThreeAfterLastWeek = DATEADD(d,35,@WhatDate)

  AND

  UNION ALL
  SELECT *
  FROM dbo.DisplayCurrentWeekDays(@ThreeAfterLastWeek)

  IF anyone cares

  Reply
• 

  Manas

  October 16, 2008 11:24 pm

  I fixed it; I believe it just for case sensitive, the first latter of column name must be same as you declare on table ,

  thanks

  Reply
• 

  ADP

  January 12, 2009 2:39 pm

  CREATE FUNCTION [dbo].[ufn_get_month] (@q DATETIME)
  RETURNS @table TABLE( ID_week INT,
  Sunday DATETIME,
  Monday DATETIME,
  Tuesday DATETIME,
  Wednesday DATETIME,
  Thursday DATETIME,
  Friday DATETIME,
  Saturday DATETIME)
  /*——————————————————————————————————–
  This query returns all the days of the month containing the input date
  in a standard S,M,T,W,T,F,S type table.
  ——————————————————————————————————–*/
  AS

  BEGIN

  ——————————————————————————————————–
  –Get 1st day of month.
  ——————————————————————————————————–
  DECLARE @y VARCHAR(4),
  @m VARCHAR(2)
  SELECT @y = CAST(YEAR(@q) AS VARCHAR),
  @m = CAST(MONTH(@q) AS VARCHAR)
  SELECT @q = CAST(@y+’/’+@m+’/1′ AS DATETIME)

  ——————————————————————————————————–
  –Declare temp table
  ——————————————————————————————————–
  DECLARE @t TABLE( ID INT IDENTITY(1,1),
  ID_week INT,
  ID_datetime DATETIME,
  ID_datename VARCHAR(20))

  DECLARE @insertDate DATETIME
  SELECT @insertDate = @q

  ——————————————————————————————————–
  –Get all the days of the month
  –uses a while loop to get all the days,
  –if user has a ‘numbers’ table, this would be preferred option.
  –as using a loop isn’t strictly a set-based query type,
  –its more of a cursor type which should be avoided.
  –Cursors generally suck but sometimes the best option.
  ——————————————————————————————————–
  WHILE MONTH(@insertDate) = MONTH(@q)
  BEGIN
  INSERT INTO @t (ID_datetime,
  ID_week,
  ID_datename)
  VALUES (@insertDate,
  DATEPART(ww,@insertDate),
  DATENAME(dw,@insertDate))
  –incrament the insertion date and continue…
  SELECT @insertDate = cast((cast(@insertDate AS FLOAT) + 1) AS DATETIME)
  END

  ——————————————————————————————————–
  –Select the values using left outer joins on the week number
  –and the day number…
  ——————————————————————————————————–
  INSERT INTO @table( ID_week,
  Sunday,
  Monday,
  Tuesday,
  Wednesday,
  Thursday,
  Friday,
  Saturday)

  SELECT Ref.ID_week AS ID_week,
  su.ID_datetime AS Sunday,
  m.ID_datetime AS Monday,
  tu.ID_datetime AS Tuesday,
  we.ID_datetime AS Wednesday,
  th.ID_datetime AS Thurday,
  fr.ID_datetime AS Friday,
  sa.ID_datetime AS Saturday
  FROM
  (SELECT DISTINCT ID_week
  FROM @t) AS Ref
  LEFT OUTER JOIN @t AS su
  ON su.ID_week = Ref.ID_week
  AND DATEPART(dw,su.ID_datetime) = 1
  LEFT OUTER JOIN @t AS m
  ON m.ID_week = Ref.ID_week
  AND DATEPART(dw,m.ID_datetime) = 2
  LEFT OUTER JOIN @t AS tu
  ON tu.ID_week = Ref.ID_week
  AND DATEPART(dw,tu.ID_datetime) = 3
  LEFT OUTER JOIN @t AS we
  ON we.ID_week = Ref.ID_week
  AND DATEPART(dw,we.ID_datetime) = 4
  LEFT OUTER JOIN @t AS th
  ON th.ID_week = Ref.ID_week
  AND DATEPART(dw,th.ID_datetime) = 5
  LEFT OUTER JOIN @t AS fr
  ON fr.ID_week = Ref.ID_week
  AND DATEPART(dw,fr.ID_datetime) = 6
  LEFT OUTER JOIN @t AS sa
  ON sa.ID_week = Ref.ID_week
  AND DATEPART(dw,sa.ID_datetime) = 7

  ——————————————————————————————————–
  –Return the table
  ——————————————————————————————————–
  RETURN

  END

  Reply
• 

  Jeff Moden

  June 24, 2009 11:32 am

  Phil Factor,

  I guess I don’t really understand why one would need to use T-SQL to build a calendar for a GUI that should have a built in feature that does that as part of “the language” already.

  Reply
• 

  Jeff Moden

  June 24, 2009 11:34 am

  Phil,

  I can understand an app needing a calendar… but why not use the built in calendar tools of the language you’re writing the app in?

  Reply
• 

  gayatri

  December 16, 2009 12:13 pm

  Hi,

  could you please let me know how can we retrieve the same within date ranges.I mean I need to get the same way within a date range that user selects.I need some thing like same output as above and based on each date of weekday i need to calculate some percentages by giving the date as input.Please help me.

  Thanks in Advance

  Reply
• 

  Nirav

  April 12, 2010 1:59 am

  Hi,

  i am looking for Function which return previous 6 week Monday Date from current date .

  let me know.

  Reply
• 

  Neelakandan

  August 24, 2010 3:04 pm

  GREAT WORK

  Reply
• 

  naveen7

  January 31, 2011 7:12 pm

  Hi

  When i gave the input date as ‘2011/01/30′ or ’31 Jan 2011’ in the result set 1st Jan 2001 is not displaying.

  Reply
• 

  ahmed

  October 23, 2011 7:10 pm

  thanks a lot
  how can i solve this problem on sql server 2005
  datename(mm, getdate()) on management studio on server is october
  datename(mm, getdate()) on remote sql server 2005 is hijri date like shwaal
  can you help me please

  Reply
  • 

    madhivanan

    October 24, 2011 7:47 pm

    It means that the default language of the server is not ENGLISH. Run this to know the server’s language

    select @@language

    Reply
    • 

      ahmed

      November 1, 2011 2:07 pm

      yes the server language is not english

• 

  Parth Malhan

  July 20, 2012 11:02 am

  Hi All,

  Following Function can also be used for fetching Calendar of Whole month
  on basis of date passed.

  ALTER Function dbo.ufn_ShowCalender(@Date datetime)
  returns @Calendar table(
  Sunday char(2),
  Monday char(2),
  Tuesday char(2),
  Wednesday char(2),
  Thrusday char(2),
  Friday char(2),
  Saturday char(2))
  as
  begin
  Declare @TempTable table(dt datetime,dy char(2),dw int,wk int)
  DECLARE @startDate datetime
  DECLARE @enddate datetime

  /*Get Starting and ending Date of month*/
  set @startDate=@Date-day(@Date)+1
  set @enddate=dateadd(mm,1,@startDate)-1

  /*Gather Data for making Calendar*/
  WHILE @startDate<=@enddate
  begin
  insert INTO @TempTable
  select @startDate,datepart(day,@startDate),datepart(dw,@startDate),datepart(wk,@startDate)
  set @startDate=@startDate+1
  end

  /*Insert Calendar in Return Table*/
  insert INTO @Calendar
  select
  isnull(max(case dw when 1 then dy end),'') as Su,
  isnull(max(case dw when 2 then dy end),'') as Mo,
  isnull(max(case dw when 3 then dy end),'') as Tu,
  isnull(max(case dw when 4 then dy end),'') as We,
  isnull(max(case dw when 5 then dy end),'') as Th,
  isnull(max(case dw when 6 then dy end),'') as Fr,
  isnull(max(case dw when 7 then dy end),'') as Sa
  from @TempTable
  group by wk
  order by wk
  return
  End
  GO
  select * from dbo.ufn_ShowCalender(getdate())

  Reply
• 

  Duraiamuthan

  September 28, 2013 1:41 pm

  is it possible to get month calendar using plain ansi standard sql without using t-sql/pl-sql.
  So that it’s possible in mini dbs like sqlite which are prevalent in mobile devices.

  Reply
• 

  duraiamuthan

  September 28, 2013 1:45 pm

  is it possible get the month calendar using standard ansi sql so that sqlite users also will benifit

  Reply