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)
20 Comments. Leave new
I tested this .. Resultset is really very interesting…thank u..
bye
Regards
Wilson Gunanithi . J
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..
The code is very useful and also very easy to understand.
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,
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 ‘(‘.
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
Thanks Pinal
It worked great.
The function works good.
Thanks for the script.
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!
The function Really good.
Thanks for the script.
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
Thanks
Very very thanks
Avinash Joshi
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!! ;)
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.
how to generate the current date in Html as a heading where the current date is passed from sql…..
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.
superb
solved lot of issues
:)
cheers
Thank you so much , this is great !
Thanks dude, this is awesome!!!
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