SQL SERVER – UDF – Function to Display Current Week Date and Day – Weekly Calendar

In analytics section of our product I frequently have to display the current week dates with days. Week starts from Sunday. We display the data considering days as column and date and other values in column. If today is Friday June 8, 2007. We need script which can provides days and dates for current week. Following script will generate the required script.

DECLARE @day INT
DECLARE
@today SMALLDATETIME
SET @today = CAST(CONVERT(VARCHAR(10), GETDATE(), 101) AS SMALLDATETIME)
SET @day = DATEPART(dw, @today)
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

Above script can be converted in function.

CREATE FUNCTION
dbo.udf_DisplayCurrentWeekDateDays
(@today SMALLDATETIME)
RETURNS @WeekDateDay TABLE
(
Sunday SMALLDATETIME,
Monday SMALLDATETIME,
Tuesday SMALLDATETIME,
Wednesday SMALLDATETIME,
Thursday SMALLDATETIME,
Friday SMALLDATETIME,
Saturday SMALLDATETIME
)
AS
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

The function can be execute for any day to return the week information for the week the day belongs.
----Following will return previous week date and days
SELECT *
FROM dbo.udf_DisplayCurrentWeekDateDays(DATEADD(d,-7,GETDATE()))
GO

----Following will return current week date and days
SELECT *
FROM dbo.udf_DisplayCurrentWeekDateDays(GETDATE())
GO

----Following will return next week date and days
SELECT *
FROM dbo.udf_DisplayCurrentWeekDateDays(DATEADD(d,7,GETDATE()))
GO

Result Set: (Today)
Sunday Monday Tuesday
———————– ———————– ———————–
2007-08-05 00:00:00.000 2007-08-06 00:00:00.000 2007-08-07 00:00:00.000
Wednesday Thursday Friday
———————– ———————– ———————–
2007-08-08 00:00:00.000 2007-08-09 00:00:00.000 2007-08-10 00:00:00.000
Saturday
———————–
2007-08-11 00:00:00.000

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

About these ads

22 thoughts on “SQL SERVER – UDF – Function to Display Current Week Date and Day – Weekly Calendar

  1. I found this code when I was searching for getting day of the week from today’s date
    I have to find out total transactions in the table in the last week .
    How to get it? please will u guide?
    bye..
    have a nice time..

    Like

  2. This function works great. Thanks,
    Have a question. Would you advise in case I have question? If yes, where can I submit my questions…
    Thanks again,

    Like

  3. I create the UDF successfully. I then run this:

    –Following will return previous week date and days
    SELECT *
    FROM dbo.udf_DisplayCurrentWeekDateDays(DATEADD(d,-7,GETDATE()))

    And get this error:

    Server: Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near ‘(‘.

    Like

  4. Thanks for the code.

    If some one needs just today’s day:

    SELECT
    CASE DATEPART(dw,getdate())
    WHEN 2 THEN ‘MON’
    WHEN 3 THEN ‘TUE’
    WHEN 4 THEN ‘WED’
    WHEN 5 THEN ‘THU’
    WHEN 6 THEN ‘FRI’
    WHEN 7 THEN ‘SAT’
    WHEN 1 THEN ‘SUN’
    ELSE ‘ALL’
    END

    Like

  5. Is there an easy way to take today’s date and find out what week it falls into?

    Example: 7/11/2007 falls into: Week #28

    Would appreciate any help on this!

    Like

  6. I took the function and wrote another one on top of it to make a calendar….

    You can call it like this:

    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)

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

    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 @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)

    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(@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)
    ) 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

    Like

  7. Pingback: SQL SERVER - UDF to Return a Calendar for Any Date for Any Year Journey to SQL Authority with Pinal Dave

  8. Hello!!!

    What about months of six weeks, like past march of 2008?
    I had to correct the code to diaplay days 30, 31. and OK!! ;)

    Like

  9. i am trying to use this function but running into the very issue a previous poster had..

    Server: Msg 170, Level 15, State 1, Line 2
    Line 2: Incorrect syntax near ‘(‘.

    please help..

    this function is exactly what i need for this particular project.

    Like

  10. PANKAJ BACHHAV :
    Thanks Pinal Dave sir, Your UDF really helped me but there is one problem ON ’30 Nov 2012′ it is not working properly it hides first row or it not returns first row please replay as early as possible

    Like

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #032 | Journey to SQL Authority with Pinal Dave

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