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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL
Next Post
SQL SERVER – Spatial Database Definition and Research Documents

Related Posts

20 Comments. Leave new

  • Wilson Gunanithi . J
    June 16, 2007 10:55 pm

    I tested this .. Resultset is really very interesting…thank u..
    bye

    Regards

    Wilson Gunanithi . J

    Reply
  • really nice code using it I created a function in my database
    June 21, 2007 5:51 am

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

    Reply
  • The code is very useful and also very easy to understand.

    Reply
  • 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,

    Reply
  • 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 ‘(‘.

    Reply
  • 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

    Reply
  • Thanks Pinal

    It worked great.

    Reply
  • The function works good.
    Thanks for the script.

    Reply
  • 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!

    Reply
  • The function Really good.
    Thanks for the script.

    Reply
  • 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

    Reply
  • Avinash Joshi
    March 1, 2008 4:43 pm

    Thanks

    Very very thanks

    Avinash Joshi

    Reply
  • 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!! ;)

    Reply
  • SprocsNewbie
    April 10, 2008 4:43 pm

    Anyone knows how to make a function that will display weekly dates for a month. I mean it displays the dates per week in a rows.

    Reply
  • how to generate the current date in Html as a heading where the current date is passed from sql…..

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

    Reply
  • superb
    solved lot of issues
    :)
    cheers

    Reply
  • Thank you so much , this is great !

    Reply
  • Thanks dude, this is awesome!!!

    Reply
  • 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

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version