SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get date for iteration of a DOW


Get date for iteration of a DOW

Author
Message
Beedle
Beedle
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 215
Comments posted to this topic are about the item Get date for iteration of a DOW
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)

Group: General Forum Members
Points: 860540 Visits: 47142
It's broke...

DECLARE
@Year SMALLINT = 2017
, @Month TINYINT = 10 -- 1-12
, @DOW TINYINT = 1 -- Sunday = 0, Monday = 1, ect...
, @Itr TINYINT = 1 -- iteration of specified DOW
BEGIN
DECLARE @1st DATE = CONVERT(VARCHAR(2),@Month)+'/1/' + CONVERT(VARCHAR(4),@Year);
DECLARE @RtrnDt DATE = DATEADD(DAY,((CEILING((CONVERT(DECIMAL(8, 2),17) % DATEPART(dw, @1st)) / 10) * 7) + (@DOW+((@Itr-2)*7))+1) - DATEPART(dw, @1st), @1st);
SELECT @RtrnDt
END



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Beedle
Beedle
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 215
you're right - my bad, the "DECLARE @RtrnDt" should be:
DECLARE @RtrnDt DATE = DATEADD(DAY,((CEILING((CONVERT(DECIMAL(8, 2),17) % DATEPART(dw, @1st)) / 10) * 7) + ((@Itr*7)+@DOW+1)-7) - DATEPART(dw, @1st), @1st);
Beedle
Beedle
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 215
Wait, still wrong... I'll reply once I fix it
Beedle
Beedle
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 215
Full disclosure, I attempted to convert from this function I wrote 10 years ago, but am getting the math wrong now. - I'm pretty sure each part in this script works, I'm just not applying the new variables correctly.
CREATE FUNCTION [dbo].[udfConvertFromGMT]
(
@InputDate DATETIME
)
RETURNS DATETIME
AS
/*
Script Date: 08/11/2009
Author: Sean Senneka
Purpose: Converts from GMT to CST (or CDT if date is during daylight savings time)
*/
--DECLARE @InputDate datetime
--Select @InputDate = '10/4/1985 08:00:00'
BEGIN
DECLARE @converted_date DATETIME;
DECLARE @DST_Start DATETIME;
DECLARE @DST_End DATETIME;
DECLARE @GMT_Offset_Destination INT;
-- Get start and end dates for daylight savingstime for the year in question
SELECT @DST_Start = CASE
WHEN DATEPART(YEAR, @InputDate) >= 2007 THEN
'3/1/' + CONVERT(VARCHAR, DATEPART(YEAR, @InputDate))
WHEN DATEPART(YEAR, @InputDate)
BETWEEN 1986 AND 2006 THEN
'4/1/' + CONVERT(VARCHAR, DATEPART(YEAR, @InputDate))
ELSE -- Year prior to 1986

'4/30/' + CONVERT(VARCHAR, DATEPART(YEAR, @InputDate))
END,
@DST_End = CASE
WHEN DATEPART(YEAR, @InputDate) >= 2007 THEN
'11/1/' + CONVERT(VARCHAR, DATEPART(YEAR, @InputDate))
ELSE
'10/31/' + CONVERT(VARCHAR, DATEPART(YEAR, @InputDate))
END;

SELECT @DST_Start
=
--2007 till present get second Sunday of March, 1986-2006 get first Sunday of April, prior to 1986 get last sunday in April

CASE
WHEN DATEPART(YEAR, @InputDate) >= 2007 THEN
DATEADD(
HOUR,
8,
DATEADD(
DAY,
((CEILING((CAST(17 AS DECIMAL(8, 2)) % DATEPART(dw, @DST_Start)) / 10) * 7) + 8)
- DATEPART(dw, @DST_Start),
@DST_Start
)
)
WHEN DATEPART(YEAR, @InputDate)
BETWEEN 1986 AND 2006 THEN
DATEADD(
HOUR,
8,
DATEADD(
DAY,
((CEILING((CAST(17 AS DECIMAL(8, 2)) % DATEPART(dw, @DST_Start)) / 10) * 7) + 1)
- DATEPART(dw, @DST_Start),
@DST_Start
)
)
ELSE -- Year prior to 1986

DATEADD(HOUR, 8, DATEADD(DAY, 0 - (DATEPART(dw, @DST_Start) - 1), @DST_Start))
END,

--2007 till present get first Sunday of November, else get last Sunday of October

@DST_End
= CASE
WHEN DATEPART(YEAR, @InputDate) >= 2007 THEN
DATEADD(
SECOND,
-1,
DATEADD(
HOUR,
8,
DATEADD(
DAY,
((CEILING((CAST(17 AS DECIMAL(8, 2)) % DATEPART(dw, @DST_End))
/ 10
) * 7
) + 1
) - DATEPART(dw, @DST_End),
@DST_End
)
)
)
ELSE
DATEADD(SECOND, -1, DATEADD(HOUR, 8, DATEADD(DAY, 0 - (DATEPART(dw, @DST_End) - 1), @DST_End)))
END,
@GMT_Offset_Destination = -360;

RETURN CASE
WHEN @InputDate
BETWEEN @DST_Start AND @DST_End THEN
DATEADD(MINUTE, @GMT_Offset_Destination + 60, @InputDate)
ELSE
DATEADD(MINUTE, @GMT_Offset_Destination, @InputDate)
END;

END;

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)

Group: General Forum Members
Points: 860540 Visits: 47142
Take your time. You can't think if you're in a rush. Put it up for a couple of days and come back to it.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jonathan.crawford
jonathan.crawford
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4396 Visits: 1309
I am going to offer an alternative for you, that I think is a lot more readable/understandable.

First, note the DATEFROMPARTS() function, which you would use since you're just feeding in a year and month, that's new as of 2012. Secondly, the idea is that you only really need to find the first Monday (or whatever day we're looking for), if you want the next one then just add a week. Third, since you're using a zero-based array for @DOW, the DATEPART(dw,@1st) function will not equal zero ever, so we have to adjust @DOW when comparing.
SET DATEFIRST 7 --week starts with Sunday, default for English, but set to make sure
DECLARE
@Year SMALLINT = 2018
, @Month TINYINT = 2 -- 1-12
, @DOW TINYINT = 0 -- Sunday = 0, Monday = 1, ect...
, @Itr TINYINT = 1 -- iteration of specified DOW
BEGIN
DECLARE @1st DATE = DATEFROMPARTS(@Year,@Month,1)
DECLARE @RtrnDt DATE
--if our day of the week for the 1st is the same as we are looking for, then return it
--we can find the next same day of the week by adding a week to that day
IF DATEPART(dw,@1st) = (@DOW + 1) --adjusting to accomodate zero-based array
BEGIN
SET @RtrnDt = DATEADD(ww,@Itr-1,@1st);
END
--if our @DOW is later in the week, offset from the 1st by the number of days larger and add in our week iterator
ELSE IF DATEPART(dw,@1st) < (@DOW + 1)
BEGIN
SET @RtrnDt = DATEADD(ww,@Itr-1,DATEADD(dd,(@DOW+1)-DATEPART(dw,@1st),@1st));
END
ELSE -- the @DOW is earlier in the week, subtract the value from the 1st and add in week iterator
BEGIN
SET @RtrnDt = DATEADD(ww,@Itr-1,DATEADD(dd,DATEPART(dw,@1st)-(@DOW),@1st));
END
SELECT @RtrnDt;
END


Hope that helps if you can't get your math working.


-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search