February 20, 2018 at 8:18 am
Comments posted to this topic are about the item Get date for iteration of a DOW
February 20, 2018 at 2:58 pm
It's broke...
DECLARE
@Year
SMALLINT= 2017
, @MonthTINYINT= 10 -- 1-12
, @DOW TINYINT= 1 -- Sunday = 0, Monday = 1, ect...
, @ItrTINYINT= 1 -- iteration of specified DOW
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
END
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2018 at 6:34 pm
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);
February 20, 2018 at 6:43 pm
Wait, still wrong... I'll reply once I fix it
February 20, 2018 at 7:13 pm
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;
February 20, 2018 at 7:31 pm
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
Change is inevitable... Change for the better is not.
February 21, 2018 at 6:23 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply