Let me know if you find that it breaks with anything....(I know it won't be correct if you specify an invalid @Itr, just want to know if the math doesn't work for any dates checked)
2017-12-29
1,080 reads
Get date for iteration of a DOW
CREATE FUNCTION GetDateForDOW ( --DECLARE @YearSMALLINT= 2018 , @MonthTINYINT= 11 -- 1-12 , @DOW TINYINT= 4 -- Sunday = 0, Monday = 1, ect... , @ItrTINYINT= 4 -- iteration of specified DOW ) RETURNS DATE AS BEGIN DECLARE@1stDATE = CONVERT(VARCHAR(2),@Month)+'/1/' + CONVERT(VARCHAR(4),@Year); DECLARE@RtrnDtDATE = DATEADD(DAY,((CEILING((CONVERT(DECIMAL(8, 2),17) % DATEPART(dw, @1st)) / 10) * 7) + (@DOW+((@Itr-2)*7))+1) - DATEPART(dw, @1st), @1st); --SELECT @RtrnDt RETURN@RtrnDt; END GO
Related content
Compute Holiday Day of the Month for any Year
This script provides the day of the month for the standard bank holidays for any given year. This is especially useful for computing the workday after a holiday.
2017-05-24 (first published: 2017-05-10)
490 reads
SQL Window Functions Series: LAG() and LEAD()
Dive deep into the powerful SQL window functions, LAG() and LEAD(). Explore their intricacies, discover real-world examples, and avoid common pitfalls.
2023-12-11
4,553 reads
Combine DATE and TIME to DATETIME2 in SQL Server
The most common method on the internet for combining DATE and TIME columns in SQL Server is incorrect. This article demonstrates why that and other methods are incorrect and two lesser known high performance methods that produce correct results even for the "edge cases".
2023-08-07
11,923 reads
How to Find the Start and End Dates for the Current Week (and more)
Learn to calculate Start / First of Week, End of Week, Start of Next Week, Year, Quarter, and Month of the week, Week Numbers and more in T-SQL (Jeff Moden)
2022-07-13
22,963 reads