Get Saturday from the week specified in column
-
Hello everyone,
I have such an issue. I have a column S1SDAT that contains dates within the week period normally from what I see Mon - Fri and then I need to create another column 'AR_WEEK_I8YYMD' that will return Saturday of the week from S1SDAT .
For example:
should turn into '20220716'
SELECT S1SDAT AS 'AR_WEEK_I8YYMD',
The way how I usually do it is with this query
SELECT DATEADD(DAY , 7-DATEPART(WEEKDAY,GETDATE()),S1SDAT) AS 'AR_WEEK_I8YYMD',
But for some reason right now I am getting an error "Arithmetic overflow error converting expression to data type datetime." . Does someone may know what I am doing wrong?
Please let me know.
Thank you.
-
I use a consistent method for any day, so I don't use techniques like the one given.
Instead, I recommend:
DATEADD(DAY, -DATEDIFF(DAY, 5, S1SDAT) % 7 + 7, S1SDAT)
for example:
SELECT
S1SDAT,
DATEADD(DAY, -DATEDIFF(DAY, 5, S1SDAT) % 7 + 7, S1SDAT)
FROM ( VALUES(CAST('20220714' AS date)) ) AS dates(S1SDAT)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
The idea being that you can change the starting day to adjust for any day of the week. 0=Mon, 1=Tues, ..., 5= Sat, 6=Sun.
For example, if you wanted the next Tuesday, you would do this:
DATEADD(DAY, -DATEDIFF(DAY, 1, S1SDAT) % 7 + 7, S1SDAT)
Note that nothing else in the calc needs to be changed.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Scott
I am getting an error "Arithmetic overflow error converting expression to data type datetime."
- JeremyU wrote:
Hello everyone,
I have such an issue. I have a column S1SDAT that contains dates within the week period normally from what I see Mon - Fri and then I need to create another column 'AR_WEEK_I8YYMD' that will return Saturday of the week from S1SDAT .
For example:
should turn into '20220716'
SELECT S1SDAT AS 'AR_WEEK_I8YYMD',
The way how I usually do it is with this query
SELECT DATEADD(DAY , 7-DATEPART(WEEKDAY,GETDATE()),S1SDAT) AS 'AR_WEEK_I8YYMD',
But for some reason right now I am getting an error "Arithmetic overflow error converting expression to data type datetime." . Does someone may know what I am doing wrong?
Please let me know.
Thank you.
Ok... which Saturday do you want to be returned if the "current" day of the week is a Saturday or a Sunday?
Another way of looking at it is, which day of the week do YOUR weeks start on? Based on that, Scott's mostly correct answer may or may not actually be correct.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If it is Saturday 07/30 for example, I want it to be 7/30.
7/31 should be 08/06.
- JeremyU wrote:
Scott
I am getting an error "Arithmetic overflow error converting expression to data type datetime."
Almost there but you post above brings to question... what is the datatype for your S1SDAT column. I'd almost be willing to wager that it's an INT and not a temporal datatype. Is it an INT? If not, what datatype is 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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok, Jeremy... If you look in my signature line just below the body of this post, you'll find a link with the word "(fnTally)" in it. Go to that article and get the fnTally function. It's a simple numeric sequence generator that will start at either "0" or "1" and count up to whatever INT you want. I use it for more things than Carter has pills. Generating dates for demonstration purposes is one of the many.
Install that in your database and then run the following code, which creates 31 days of dates starting on the 16th of July so that you can see what I mean and easily check date formulas for accuracy. Once you've installed the fnTally function, then run this code which uses the function to do as I said.
Of course, all of this depends on your "date" column actually being a temporal datatype and not just an INT. We can fix that but the following doesn't consider that problem.
And, if this looks right by you, just substitute your S1SDat column name for the c.CalDate in the "Sat" formula below and Bob's your uncle.
SELECT c.CalDate --Just the calenar date
,Dow = DATENAME(dw,c.CalDate) --Just the calendar Day of the Week
,Sat = DATEADD(dd,DATEDIFF(dd,6,c.CalDate)/7*7+6,6) --Use this formula.
,DowSat = DATENAME(dw,DATEADD(dd,DATEDIFF(dd,6,c.CalDate)/7*7+6,6)) --Just to prove it's a Saturday
,ScottSat = DATEADD(DAY, -DATEDIFF(DAY, 5, c.CalDate) % 7 + 7, c.CalDate)
,ScottDoW = DATENAME(dw,DATEADD(DAY, -DATEDIFF(DAY, 5, c.CalDate) % 7 + 7, c.CalDate))
FROM dbo.fnTally(0,30)t
CROSS APPLY (VALUES(DATEADD(dd,t.N,'16 JUL 2022')))c(CalDate)
;That produces the following output and I've Blue hi-lited the 30th and 31st of July, which are the two dates you gave your example for. You can see that the "Sat" column is correct and Scott's code is one day off like I thought it might be. The formula for the "Sat" column in the code above is the formula you want because your weeks don't start on Mondays... they start on Sundays. The "Sat" formula simply counts the number of whole weeks starting on a known Sunday, and then add 6 to that.
As a bit of a sidebar, I hate the fact that a whole lot of data warehouse experts have insisted on using INTs to represent dates. That may have been fine in the 60's through the early 90's to save on memory and disk space but it kills easy date calculations like this one. They may have reason I don't know of for continuing the practice but I just can't see doing it any more.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I am really sorry but I don't think I am allowed to install that in my database. Maybe there is a different way...
- JeremyU wrote:
I am really sorry but I don't think I am allowed to install that in my database. Maybe there is a different way...
Who knows. They might have something similar already installed. If they don't, you might want to submit it as an idea because it'll make a whole bunch of things a whole lot easier. It's not like a scalar or mTVF (multi-statement Table Valued Function) that will slow the works down. It's a high performance iTVF (inline Table Valued Function).
Give me a couple of minutes and I'll convert it to something you don't need to install that will be as fast.
And you don't actually need to install anything. You have the formula now and I listed the run results. Anyway, I'll be back shortly...
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok, Jeremy... the following code will do the same thing as above but without you having to install the Tally function. Again, you don't need all of this code. You just need the formula for the "Sat" column and change the c.CalDate to your S1SDAT column name.
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
,Tally(N) AS (
SELECT 0 UNION ALL
SELECT TOP (30) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E1 a, E1 b
)
SELECT c.CalDate --Just the calenar date
,Dow = DATENAME(dw,c.CalDate) --Just the calendar Day of the Week
,Sat = DATEADD(dd,DATEDIFF(dd,6,c.CalDate)/7*7+6,6) --Use this formula.
,DowSat = DATENAME(dw,DATEADD(dd,DATEDIFF(dd,6,c.CalDate)/7*7+6,6)) --Just to prove it's a Saturday
,ScottSat = DATEADD(DAY, -DATEDIFF(DAY, 5, c.CalDate) % 7 + 7, c.CalDate)
,ScottDoW = DATENAME(dw,DATEADD(DAY, -DATEDIFF(DAY, 5, c.CalDate) % 7 + 7, c.CalDate))
FROM Tally t
CROSS APPLY (VALUES(DATEADD(dd,t.N,'16 JUL 2022')))c(CalDate)
;--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, thank you but I am getting the very same error when using this query
DATENAME(dw,DATEADD(dd,DATEDIFF(dd,6,S1SDAT)/7*7+6,6))
Arithmetic overflow error converting expression to data type datetime.
-
You haven't answered my question... what is the datatype of your S1SDAT column? If it's an INT, BIGINT, DECIMIAL(X,0) or any other datatype that doesn't have the word DATE in it, that's a problem and we can fix it. What datatype is the S1SDAT column???
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you for your initial reply. I casted to CHAR. It fixed itself.
Thanks.
- JeremyU wrote:
Thank you for your initial reply. I casted to CHAR. It fixed itself.
Thanks.
That would do it, for sure. I'm still amazed that someone would make the mistake of storing dates as INTs these days. Of course, they never fixed it in the job history in MSDB. 🙁
Also, if you have a huge amount of data, you can be a little better performance by using integer math to convert it instead of text-based conversions.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply