October 24, 2015 at 8:42 pm
dmbaker (10/23/2015)
Thanks for the article...5 stars (especially since you didn't say "performant" anywhere) 😀
Thanks for the feedback and the stars. I appreciate it.
I'm curious as to why the slang/lingo word "performant" sit's in the craw of so many people, though. To quote the first item that pops up when you Google the definition...
Performant is a word that was made up by software developers to describe software that performs well, in whatever way you want to define performance. It is a word that is not in the dictionary yet, but I think it should be.
I agree and I'll add that that's the way new words end up in the dictionary. 😉
On that same note, have you ever told someone to Google something? If so, then you're just as bad as people who say "performant" because Google is a proper noun and not a verb. :w00t:
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2015 at 9:57 am
To the best of my knowledge, I know of no temporal system that considers midnight to be at the end of the day. It always marks the beginning of a day. For example, there is no 24:00 for time even in the military.
The project managers I work with are often confused. We have asked them to enter 23:59 or 00:01 to be clear which night of the weekend I'll be working on their conversion.
412-977-3526 call/text
October 26, 2015 at 10:33 am
robert.sterbal 56890 (10/26/2015)
To the best of my knowledge, I know of no temporal system that considers midnight to be at the end of the day. It always marks the beginning of a day. For example, there is no 24:00 for time even in the military.
The project managers I work with are often confused. We have asked them to enter 23:59 or 00:01 to be clear which night of the weekend I'll be working on their conversion.
They understand the concept of 24 hour time but they can't handle midnight. Remarkable.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2015 at 6:54 am
SwePeso (4/8/2013)
However, the fix is really simple. Since all we need in an anchor date of a monday, write this case statement.
Thanks for the insight.
December 8, 2015 at 8:49 am
Kudos to "t-clausen.dk" and thanks to Jeff Moden for the explanation. After a brief read (post search) I wanted to try and create a similar "elegant" equation that would work for a customer of mine that does not use the ISO week, but rather "the first week of the year is whatever week the first day of the year occurs in". I have to generate a material identifier using the year and week as part of the generated ID for this customer. Unfortunately lacking the patience and time in which to thoroughly understand the formula, I decided to create something from scratch quickly enough to get the current (and soon to be "broken") ISO method in place, replaced. My first attempt was dismal, as my incomplete understanding of the formula led me to try to use the days in a year with a calculated "overage" to determine which days at the end of December would fall into the new year. I realized the driving factor is the first day of the coming year and the days in a year just didn't matter. After that I was able to create a relatively simplistic way to get what I needed and did want to share with you all to pick apart to your hearts content.
DECLARE @OrderDate DATETIME
DECLARE @OrderYear VARCHAR(2)
DECLARE @OrderWeek VARCHAR(2)
SET @OrderDate = '1/1/2000'
SET NOCOUNT ON
/******************************************************************************************/
/* The rule is "whichever week day one of the new year falls on is considered Week 1" */
/* Calculate the difference in days between Order Date and First Day of Next Year (FDONY) */
/* if that difference is less than the FDONY day of the week minus one that means the */
/* Order Date is part of the FDONY week and therefore will be week 1 of the next year */
/* THIS DOES ASSUME "SET DATEFIRST 7" U.S. English default */
/******************************************************************************************/
--DECLARE @FDONY_Date DATETIME = CAST('1/1/' + CAST(DATEPART(YYYY, @OrderDate) + 1 AS VARCHAR) AS DATETIME)
--DECLARE @DaysDiff_OrderDate_FDONY INT = DATEDIFF(DD, @OrderDate, @FDONY_Date)
--DECLARE @DayOfWeek_FDONY INT = DATEPART(DW, @FDONY_Date)
DECLARE @AdjustCalc INT = 0
--IF @DaysDiff_OrderDate_FDONY <= @DayOfWeek_FDONY - 1 SET @AdjustCalc = 1
--w/o variables
IF (DATEDIFF(DD, @OrderDate, (CAST('1/1/' + CAST(DATEPART(YYYY, @OrderDate) + 1 AS VARCHAR) AS DATETIME)))) <= (DATEPART(DW, (CAST('1/1/' + CAST(DATEPART(YYYY, @OrderDate) + 1 AS VARCHAR) AS DATETIME)))) - 1
SET @AdjustCalc = 1
SET @OrderWeek = ( CASE @AdjustCalc WHEN 1 THEN '01' ELSE RIGHT( '0' + CAST(DATEPART(WK, @OrderDate) AS VARCHAR), 2) END )
SET @OrderYear = RIGHT( '0' + CAST(DATEPART(YYYY, @OrderDate) + @AdjustCalc AS VARCHAR), 2)
December 8, 2015 at 9:32 am
Interesting...but have a play with this, which could be converted into an inline table-valued function:
-- SET DATEFIRST 7 = SUNDAY
SELECT DATENAME(DW,'18991231') -- sunday
-- count the number of whole weeks between '18991231' (a sunday) and '20160101'
SELECT DATEDIFF(WEEK,'18991231','20160101')
-- 6052
-- add this figure to '18991231' to get the start of the week (the sunday) containing '20160101'
SELECT DATEADD(WEEK,6052,'18991231')
-- 2015-12-27 00:00:00.000
-- combine these two expressions
SELECT DATEADD(WEEK,DATEDIFF(WEEK,'18991231','20160101'),'18991231')
-- 2015-12-27 00:00:00.000
-- test with a bunch of dates
SELECT
FirstDay,
DATENAME(DW,FirstDay),
x.FDONY,
DATENAME(DW,x.FDONY)
FROM (VALUES
(CAST('20180101' AS datetime)),
('20170101'),
('20160101'),
('20150101'),
('20140101'),
('20130101'),
('20120101'),
('20110101')) d (FirstDay)
CROSS APPLY (SELECT FDONY = DATEADD(WEEK,DATEDIFF(WEEK,'18991231',FirstDay),'18991231')) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 8, 2015 at 2:31 pm
Thank you I did manage to take a good run at this but feel I have fallen short on the result. I know there is some better way to get the week number out of what you shared, and that still does not account for having to bump the year up to properly set the identifier for the material code. I will take another look when time permits, thanks again for your reply.
DECLARE @OrderDate DATETIME = '12/28/2008'
SELECT CASE WHEN (DATEDIFF( DD, DATEADD(WEEK,DATEDIFF(WEEK,'18991231', (CAST('1/1/' + CAST(DATEPART(YYYY, @OrderDate) + 1 AS VARCHAR) AS DATETIME) )), '18991231'), @OrderDate )) >= 0 THEN (RIGHT( '0' + CAST(DATEPART(YYYY, @OrderDate) + 1 AS VARCHAR), 2)) + '01' ELSE (RIGHT( '0' + CAST(DATEPART(YYYY, @OrderDate) AS VARCHAR), 2)) + RIGHT( '0' + CAST(DATEPART(WK, @OrderDate) AS VARCHAR), 2) END AS YearWeek
December 9, 2015 at 2:20 am
chill-1062987 (12/8/2015)
Thank you I did manage to take a good run at this but feel I have fallen short on the result. I know there is some better way to get the week number out of what you shared, and that still does not account for having to bump the year up to properly set the identifier for the material code. I will take another look when time permits, thanks again for your reply.
DECLARE @OrderDate DATETIME = '12/28/2008'
SELECT CASE WHEN (DATEDIFF( DD, DATEADD(WEEK,DATEDIFF(WEEK,'18991231', (CAST('1/1/' + CAST(DATEPART(YYYY, @OrderDate) + 1 AS VARCHAR) AS DATETIME) )), '18991231'), @OrderDate )) >= 0 THEN (RIGHT( '0' + CAST(DATEPART(YYYY, @OrderDate) + 1 AS VARCHAR), 2)) + '01' ELSE (RIGHT( '0' + CAST(DATEPART(YYYY, @OrderDate) AS VARCHAR), 2)) + RIGHT( '0' + CAST(DATEPART(WK, @OrderDate) AS VARCHAR), 2) END AS YearWeek
If you can post up a little sample data (this article [/url]will show you how), I'm sure we can help.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 9, 2015 at 11:20 am
Hello again and thank you for your continued interest in this in advance. I want to clarify a couple of things real quick in the interests of better understanding. The system I am currently (and hopefully somewhat temporarily) saddled with maintaining uses a procedural process to generate a material identifier, a portion of which is a fixed format "YYWK" representing the last 2 digits of the year and another 2 for the week of the year. Our process is driven by a weekly order cycle, currently with a Monday date representing the order week, but could be designated as any day of the week. This date drives the population of the "YYWK", which is the same for every ID for that week. If the week is the first week of the year, the year value should reflect the year that Jan 1st is in, not necessarily the year of the date given. I did create and post what for now is an acceptable solution, but for both academic interest and potential future improvement I wanted to take this further.
The date data to drive this could be any date, forgive my awkward usage of a CTE (which was fun) to create the test data.
IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
CREATE TABLE #TestTable
(
CalendarKey INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CalendarDate DATETIME,
ISO_YearWeek VARCHAR(4),
Adj_YearWeek VARCHAR(4)
)
SET IDENTITY_INSERT #TestTable ON
;WITH tDates AS
(
SELECT 1 AS CalendarKey,
CAST('12/26/1999' AS DATETIME) AS CalendarDate,
NULL AS ISO_YearWeek,
NULL AS Adj_YearWeek
UNION ALL
SELECT CalendarKey + 1,
CalendarDate + 1 AS CalendarDate,
NULL AS ISO_YearWeek,
NULL AS Adj_YearWeek
FROM tDates
WHERE CalendarDate < '1/4/2020'
)
INSERT #TestTable
(
CalendarKey,
CalendarDate,
ISO_YearWeek,
Adj_YearWeek
)
SELECT CalendarKey,
CalendarDate,
ISO_YearWeek,
Adj_YearWeek
FROM tDates
OPTION (MAXRECURSION 7315)
SET IDENTITY_INSERT #TestTable OFF
Below find the equivalent of what is in place today (ISO), with the somewhat unsatisfactory replacement (Jan 1st based) to determine the "YYWK" value:
UPDATE #TestTable
SET ISO_YearWeek =
CASE
WHEN (MONTH(CalendarDate) = 12 AND DATEPART(ISOWW, CalendarDate) = 1)
THEN RIGHT(CONVERT(CHAR(4), YEAR(CalendarDate) + 1), 2)
ELSE RIGHT(CONVERT(CHAR(4), YEAR(CalendarDate)), 2)
END +
RIGHT('0' + CAST(CONVERT(INT, DATEPART(ISOWW, CalendarDate)) AS VARCHAR), 2)
UPDATE #TestTable
SET Adj_YearWeek =
CASE
WHEN (DATEDIFF( DD, DATEADD(WEEK,DATEDIFF(WEEK,'18991231', (CAST('1/1/' + CAST(DATEPART(YYYY, CalendarDate) + 1 AS VARCHAR) AS DATETIME) )), '18991231'), CalendarDate )) >= 0
THEN (RIGHT( '0' + CAST(DATEPART(YYYY, CalendarDate) + 1 AS VARCHAR), 2)) + '01'
ELSE (RIGHT( '0' + CAST(DATEPART(YYYY, CalendarDate) AS VARCHAR), 2)) + RIGHT( '0' + CAST(DATEPART(WK, CalendarDate) AS VARCHAR), 2)
END
SELECT CalendarDate,
DATENAME(DW,CalendarDate) AS Weekday,
ISO_YearWeek,
Adj_YearWeek
FROM #TestTable
The results illustrate the wanted value in the "Adj" column, and also enlightened me that even our current ISO method would fail if our weekly order date was changed to a Sunday.
CalendarDateWeekdayISO_YearWeekAdj_YearWeek
----------------------- ------------------------------------------------------
2008-12-26 00:00:00.000 Friday08520852
2008-12-27 00:00:00.000 Saturday08520852
2008-12-28 00:00:00.000 Sunday08520901
2008-12-29 00:00:00.000 Monday09010901
2008-12-30 00:00:00.000 Tuesday09010901
2008-12-31 00:00:00.000 Wednesday09010901
2009-01-01 00:00:00.000 Thursday09010901
2009-01-02 00:00:00.000 Friday09010901
2009-01-03 00:00:00.000 Saturday09010901
2009-01-04 00:00:00.000 Sunday09010902
2009-01-05 00:00:00.000 Monday09020902
February 8, 2021 at 2:18 pm
Is this still the best way to do this in 2021? Google landed me at another SSC Forum from 2005, which I can't find anymore, and now I'm using this
with iso_dts_cte(yr, mo, wk) as (
select * from (values ('2020', '12', '50'),
('2020', '12', '51'),
('2020', '12', '52'),
('2020', '12', '53'),
('2021', '01', '01'),
('2021', '01', '02'),
('2021', '01', '03')) v(yr, mo, wk))
select iso.*, v.*
from iso_dts_cte iso
cross apply (values (cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk-1)*7) as date),
cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk)*7)-1 as date))) v(start_dt, end_dt);
yrmowkstart_dtend_dt
202012502020-12-072020-12-13
202012512020-12-142020-12-20
202012522020-12-212020-12-27
202012532020-12-282021-01-03
202101012021-01-042021-01-10
202101022021-01-112021-01-17
202101032021-01-182021-01-24
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 8, 2021 at 2:34 pm
Is this still the best way to do this in 2021? Google landed me at another SSC Forum from 2005, which I can't find anymore, and now I'm using this
with iso_dts_cte(yr, mo, wk) as (
select * from (values ('2020', '12', '50'),
('2020', '12', '51'),
('2020', '12', '52'),
('2020', '12', '53'),
('2021', '01', '01'),
('2021', '01', '02'),
('2021', '01', '03')) v(yr, mo, wk))
select iso.*, v.*
from iso_dts_cte iso
cross apply (values (cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk-1)*7) as date),
cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk)*7)-1 as date))) v(start_dt, end_dt);yrmowkstart_dtend_dt
202012502020-12-072020-12-13
202012512020-12-142020-12-20
202012522020-12-212020-12-27
202012532020-12-282021-01-03
202101012021-01-042021-01-10
202101022021-01-112021-01-17
202101032021-01-182021-01-24
Do "this"? Just to be absolutely sure (I've not had enough coffee yet), what is "this"? Do you mean the conversion of an ISO week to an actual date range?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2021 at 2:50 pm
Steve Collins wrote:Is this still the best way to do this in 2021? Google landed me at another SSC Forum from 2005, which I can't find anymore, and now I'm using this
with iso_dts_cte(yr, mo, wk) as (
select * from (values ('2020', '12', '50'),
('2020', '12', '51'),
('2020', '12', '52'),
('2020', '12', '53'),
('2021', '01', '01'),
('2021', '01', '02'),
('2021', '01', '03')) v(yr, mo, wk))
select iso.*, v.*
from iso_dts_cte iso
cross apply (values (cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk-1)*7) as date),
cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk)*7)-1 as date))) v(start_dt, end_dt);yrmowkstart_dtend_dt
202012502020-12-072020-12-13
202012512020-12-142020-12-20
202012522020-12-212020-12-27
202012532020-12-282021-01-03
202101012021-01-042021-01-10
202101022021-01-112021-01-17
202101032021-01-182021-01-24Do "this"? Just to be absolutely sure (I've not had enough coffee yet), what is "this"? Do you mean the conversion of an ISO week to an actual date range?
Yes. Conversion of ISO date parts to calendar date. This code brought 25 points on Stack Overflow last week but I was kind of wondering/hoping someone had a better/newer way. 'iso_week' was added as a datepart but it doesn't seem to help with this
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 8, 2021 at 4:19 pm
Thanks. I'll look at it in a bit more detail later but my first impression is that the following line of code in the CROSSAPPLY is incorrect.
cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk)*7)-1 as date)
The gives you the start of the end date. That will perpetuate the use of things like BETWEEN or doing the silly thing of adding 23:59:59 to the end date. It would be MUCH better (IMHO) to remove the -1 at the end of the formula and make people understand that the "end date" is exclusive and is truly the first date when something is no longer true to support the much more correct criteria of WHERE SomeDateColumn >= StartDate and SomeDateColumn < EndDate (or NextStartDate, which is more technically and obviously correct).
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2021 at 6:44 pm
Thanks. I'll look at it in a bit more detail later but my first impression is that the following line of code in the CROSSAPPLY is incorrect.
cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk)*7)-1 as date)The gives you the start of the end date. That will perpetuate the use of things like BETWEEN or doing the silly thing of adding 23:59:59 to the end date. It would be MUCH better (IMHO) to remove the -1 at the end of the formula and make people understand that the "end date" is exclusive and is truly the first date when something is no longer true to support the much more correct criteria of WHERE SomeDateColumn >= StartDate and SomeDateColumn < EndDate (or NextStartDate, which is more technically and obviously correct).
Ok thank you. Awesome. Yeah, recently I can recall where 23:59:59 was bluntly applied... the OP had other issues tho. It should be one way yes. On topic it seems datefromisoparts(iso_year, iso_month, ...) or something would be nice to have
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 8, 2021 at 6:47 pm
DATEPART(ISO_WEEK, @thedate) ???
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 61 through 75 (of 77 total)
You must be logged in to reply to this topic. Login to reply