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

It gives me great pleasure to write articles like today’s one because I have received great comment from one of regular reader who has taken UDF written by me and created another UDF using that UDF which enhances functionality of it. I had written previous article about SQL SERVER – UDF – Function to Display Current Week Date and Day – Weekly Calendar. Reader of this blog and great SQL expert Dan Golden has wrote another UDF which uses UDF written by me. I thank Dan Golden for his contribution to this blog. I have modified his function a bit to remove error when it was missing displaying first or last week in few cases, however I will give full credit to him for his thought.

I also encourage my other readers to submit their articles to me, if I think your article will benefit other readers I will publish it here with your name. Do not send me article if it is not written by you.

Step 1 : Create UDF to display current week date and day – Weekly Calender.
USE AdventureWorks;
GO
CREATE FUNCTION dbo.DisplayCurrentWeekDays
(@today SMALLDATETIME)
RETURNS @WeekDateDay TABLE
(
Sunday SMALLDATETIME,
Monday SMALLDATETIME,
Tuesday SMALLDATETIME,
Wednesday SMALLDATETIME,
Thursday SMALLDATETIME,
Friday SMALLDATETIME,
Saturday SMALLDATETIME
)
AS
/*
Purpose: To return the weekly calendar for the week any date
By: Pinal Dave -- SQLAuthority.com
On: 6/8/2007
*/
BEGIN
DECLARE
@day INT
SET
@today = CAST(CONVERT(VARCHAR(10), @today, 101) AS SMALLDATETIME)
SET @day = DATEPART(dw, @today)
INSERT INTO @WeekDateDay (Sunday, Monday,
Tuesday, Wednesday, Thursday, Friday, Saturday)
SELECT 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,
DATEADD(dd, 7 - @day, @today) Saturday
RETURN
END
GO

Step 2 : Create UDF to display current month date and day – Monthly Calender
USE AdventureWorks;
GO
CREATE 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)
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)
)
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
GO

Step 3 : Use recently created UDF to get the current month ‘s calender. You can pass any date to this calender and it will return that month and years calender.
USE AdventureWorks;
GO
DECLARE @WhatDay SMALLDATETIME
SELECT @WhatDay = GETDATE(); -- you can also put here any date like 07/01/1990
SELECT 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',
ISNULL(CONVERT(NVARCHAR(20), Friday, 101), '') AS 'Friday',
ISNULL(CONVERT(NVARCHAR(20), Saturday, 101), '') AS 'Saturday'
FROM dbo.GetSQLcalendar(@WhatDay)
GO

Above query will return you monthly calender for date passed to function.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , Dan Golden

About these ads

23 thoughts on “SQL SERVER – UDF to Return a Calendar for Any Date for Any Year

  1. 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

    Like

  2. 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)

    http://www.simple-talk.com/community/blogs/philfactor/archive/2006/01/10/117.aspx

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

    Like

  3. 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

    Like

  4. 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?

    Like

  5. 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.

    Like

  6. –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′

    Like

  7. 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

    Like

  8. 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

    Like

  9. 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.

    Like

  10. 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?

    Like

  11. 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

    Like

  12. 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

    Like

  13. 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())

    Like

  14. Pingback: SQL SERVER – Weekly Series – Memory Lane – #017 | SQL Server Journey with SQL Authority

  15. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s