Today’s article is created based on wonderful contribution from Tejas Shah. Tejas is very prominent SQL Expert and .NET wizard. He has answered the query of a reader on this blog who raised the following question: how to generate the date for all the Sundays in the upcoming year. Tejas replied here with a script.
What I really liked about the script is that it is very easy to understand, and also it can be customized very quickly.
DECLARE @Year AS INT,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2010
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)
-- Creating Query to Prepare Year Data
;WITH cte AS (
SELECT 1 AS DayID,
@FirstDateOfYear AS FromDate,
DATENAME(dw, @FirstDateOfYear) AS Dayname
UNION ALL
SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.FromDate),
DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
FROM cte
WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
)
SELECT FromDate AS Date, Dayname
FROM CTE
WHERE DayName IN ('Saturday','Sunday')
/*
WHERE DayName IN ('Saturday,Sunday') -- For Weekend
WHERE DayName NOT IN ('Saturday','Sunday') -- For Weekday
WHERE DayName LIKE 'Monday' -- For Monday
WHERE DayName LIKE 'Sunday'-- For Sunday
*/
OPTION (MaxRecursion 370)
The result will be dates along with days in next column as expected.
You are welcome to contribute any script, which you think can be helpful to others.
Reference: Pinal Dave (https://blog.sqlauthority.com)
28 Comments. Leave new
Well done but we don’t need to use recursive
Here’s my script with the same result
—————————————————-
DECLARE @FirstDateOfYear DATETIME
SET @FirstDateOfYear = ‘2010-01-01’
SELECT DISTINCT DATEADD(d, number, @FirstDateOfYear),
CASE DATEPART(dw, DATEADD(d, number, @FirstDateOfYear))
WHEN 7 THEN ‘Saturday’
WHEN 1 THEN ‘Sunday’
ELSE ‘Work Day’
END
FROM master..spt_values
WHERE number BETWEEN 0 AND 364
AND (DATEPART(dw, DATEADD(d, number, @FirstDateOfYear)) = 1 OR DATEPART(dw, DATEADD(d, number, @FirstDateOfYear)) = 7)
ORDER BY DATEADD(d, number, @FirstDateOfYear)
Excellent script with well known key values
I like this one better. Well done!
Good Script Tejas!!!.
When talk comes to date script, I would also like to share one of the script to find out first and last Friday of the years. starting from Jan-06 to Dec-2020. Actually I have got this request from one of the forums and created this very quickly.
WITH CTE AS
(
select CONVERT(datetime,’01/06/2006′) as dt, datename(dw,CONVERT(datetime,’01/06/2006′)) as dy,datename(mm,CONVERT(datetime,’01/06/2006′)) as mn, YEAR(CONVERT(datetime,’01/06/2006′)) AS yr
union all
select s.dt+7 as dts,datename(dw,dt+7) as dy ,datename(mm,dt+7) as dy, YEAR(dt+7) AS yr
from cte s
where s.dt<CONVERT(datetime,'12/31/2020')
)
select MAX(dt) as dt,dy,mn,yr from cte
group by dy,mn,yr
UNION
select MIN(dt) as dt,dy,mn,yr from cte
group by dy,mn,yr
ORDER BY dt
OPTION (MaxRecursion 0)
Really very usefull scripts…!
Hi, This is great script and will be helpful in multiple ocassions (espically when join with my time dimension), But i have one question though.
Can some one explain me how
OPTION (MAXRECURSION 370); works?, Also how do you arrive at this number – 370. I am trying to understand from BoL but still not able to get it. Any help in this regard with be great.
Hi Shan,
MAXRECURSION query hint in common table expression is used to recursively execute the last statement in CTE that is joined using UNION, UNION ALL, INTERSECT or EXCEPT.
Here The number 370 is used to keep recursion under the number of days in a year (365).
Regards,
Pinal Dave
Personally, i prefer to use a Calendar table for this sort of operation.
Thanks Pinal for your detailed reply, appreciate it.
-Shan
Hi dba guy,
Your query is very nice to understand .but i have one doubt is that you used master..spt_values just for getting numbers from 0 to 364 or there is any other specific reason.
Thanks
Rahul
Pinal,
It wont work in non-English servers
So better usage is
SELECT FromDate AS Date, Dayname
FROM CTE
WHERE datepart(weekday,Fromdate) in (7,1)
hai
can anybody give me the query for retriving previous year and month details.
thankx
it is very helpful to query
i have another problem
DECLARE @date DATETIME;
SET @date = ‘2006-10-15’;
Select datename(MONTH,DateAdd(mm,-12,@date)),datename(year,DateAdd(mm,-12,@date)) union all
select datename(MONTH,DateAdd(mm,-11,@date)),datename(year,DateAdd(mm,-11,@date))
union all
select datename(MONTH,DateAdd(mm,-10,@date)),datename(year,DateAdd(mm,-10,@date)) union all
select datename(MONTH,DateAdd(mm,-09,@date)),datename(year,DateAdd(mm,-09,@date)) union all
select datename(MONTH,DateAdd(mm,-08,@date)),datename(year,DateAdd(mm,-08,@date)) union all
select datename(MONTH,DateAdd(mm,-07,@date)),datename(year,DateAdd(mm,-07,@date)) union all
select datename(MONTH,DateAdd(mm,-06,@date)),datename(year,DateAdd(mm,-06,@date)) union all
select datename(MONTH,DateAdd(mm,-05,@date)),datename(year,DateAdd(mm,-05,@date)) union all
select datename(MONTH,DateAdd(mm,-04,@date)),datename(year,DateAdd(mm,-04,@date)) union all
select datename(MONTH,DateAdd(mm,-03,@date)),datename(year,DateAdd(mm,-03,@date)) union all
select datename(MONTH,DateAdd(mm,-02,@date)),datename(year,DateAdd(mm,-02,@date)) union all
select datename(MONTH,DateAdd(mm,-01,@date)),datename(year,DateAdd(mm,-01,@date))
This is the query that retrieves data for past one year with month .
But if we want to retrieve more than past 24 months and more its not easier to query.
Is there any other solution for this comments
CREATE PROCEDURE SP_MONTH
@COUNTER INT
AS
BEGIN
DECLARE @TEMPTABLE TABLE ([MONTH NAME] VARCHAR(50), [MONTH YEAR] VARCHAR(6))
DECLARE @RESULT VARCHAR(MAX)
WHILE @COUNTER > 0
BEGIN
INSERT INTO @TEMPTABLE VALUES (DATENAME(MONTH,DATEADD(MM, -@COUNTER, GETDATE())),DATEPART(YYYY, DATEADD(MM, -@COUNTER, GETDATE())))
SET @COUNTER = @COUNTER – 1
END
SELECT * FROM @TEMPTABLE
END
EXEC SP_MONTH 24
This procedure can calculate nos of month whatever u want
DECLARE
@weekDay TINYINT,@daySaturday DATETIME,@daySunday DATETIME
SET
@weekDay = DATEPART(dw,’2010′)
SELECT
@daySaturday = DATEADD(dd,7-@weekDay,’2010′),
@daySunday = DATEADD(dd,7-@weekDay+1,’2010′)
SELECT
DATEADD(dd,(number-1)*7,@daySaturday) ‘SATURDAY’,
DATEADD(dd,(number-1)*7,@daySunday) ‘SATURDAY’
FROM
dbo.tally
WHERE
number < = 53 AND
DATEADD(dd,(number-1)*7,@daySunday) <= '01/01/2011'
Hi frnds
I have table named Holidaylist .
with columns Hid,Hname,date,month,year.I want to retrieve
secondworking day of the current month that includes
saturday and sunday as holidays
Provide me the solution
thankx in advance
Veru useful script!!
hi,
can anybody give me the query for retriving only monday of every month
This Procedure will help to get Monday in a month
CREATE PROCEDURE SP_GETMONDAYINAMONTH
@FRISTDAYOFMONTH DATETIME
AS
BEGIN
DECLARE @NOOFDAYS INT
SET @NOOFDAYS = (select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(@FRISTDAYOFMONTH) as varchar)+’-‘+cast(month(@FRISTDAYOFMONTH) as varchar)+’-01′ as datetime)))))
SELECT DISTINCT DATEADD(d, NUMBER, @FRISTDAYOFMONTH) AS DATE ,
CASE DATEPART(dw, DATEADD(d, NUMBER, @FRISTDAYOFMONTH))
WHEN 2 THEN ‘MONDAY’
ELSE ‘WORKING DAY’
END AS [DAY OF WEAK]
FROM MASTER..SPT_VALUES
WHERE NUMBER BETWEEN 0 AND @NOOFDAYS AND (DATEPART(dw, DATEADD(d, NUMBER, @FRISTDAYOFMONTH)) = 2)
ORDER BY DATEADD(d, number, @FRISTDAYOFMONTH)
END
EXEC SP_GETMONDAYINAMONTH ‘2010-04-01’
When you execute this procedure you have to pass the frist day of month in which month you want to calculate the no of Monday
Hello Kranthi,
Change the last SELECT statement of query in article as below:
SELECT FromDate AS Date, Dayname
FROM CTE
WHERE DayName IN (‘Monday’)
Although this querywould return all mondays of current year but it can be modified to retreive records only for a month.
Regards,
Pinal Dave
Would you have any idea how to get a list of dates of the start and end of each week in a variable-specified date range?
DECLARE @RangeStartDate DATETIME,
@RangeEndDate DATETIME;
SET @RangeStartDate = ‘2008-03-01’;
–SELECT @RangeStartDate;
SET @RangeEndDate = ‘2008-05-01’;
–SELECT @RangeEndDate;
Can Any one help how to calculate moving average monthly wise.
Charge ,Payment, Month, AverageCharge,AveragePayment
300 100 1 300 100
500 150 2 400 125
400 200 3 300 116.6666667
200 50 4 150 62.5
800 300 5 200 70
450 100 6 208.3333333 66.66666667
600 150 7 150 35.71428571
just like we do in excel sheet.
nice
declare @dayname table
(
dates datetime,
daynames varchar(15)
)
declare @dates datetime = ’01-01-2015′
declare @a int = 1;
while @a <= 365
BEGIN
insert into @dayname
(
dates,daynames
)
values (@dates,datename(dw,@dates))
set @dates = @dates + 1
SET @a = @a + 1
END
select * from @dayname where daynames in ('Saturday','Sunday')
Hi
I want to get same result without CTE .I need to implement in SQL server 2005 version.
Thanks & Regards
Hi
Would you have any idea how to get all Mondays from the specific year and specific month with two variables @Year, @ Month
How to insert these two column in result into an existing table I have in my Data base ? Please provide a solution for it too.