October 25, 2019 at 11:38 am
I'm trying to compare weeks based on the start date of the given week being a thursday. Meaning the week count should count the number of weeks based on a week consisting of Thursday - Wed. However that's not whats happening. I'd expect the last column to be equal to the prev because they are in the same thur-wed week. Am i making sense? The reason is i'm trying to calc employee availability and we have bi weekly rules.
declare @dayOfWeek int
,@Thur date = '2019-10-24'
,@NextMonday date = '10/28/2019'
set @dayOfWeek = datepart(dw,@Thur )
set datefirst @dayOfWeek
select @dayOfWeek
,DATEPART(week,@Thur ) [Equals 43]
,DATEPART(week,@NextMonday) [Equals 44]
October 25, 2019 at 12:00 pm
You have written a script that if you run it twice you will get a different answer:
SET DATEFIRST 1
DECLARE @dayOfWeek int,
@Thur date= '20191024',
@NextMonday date= '20191028'
SET @dayOfWeek = DATEPART(dw, @Thur)
SET DATEFIRST @dayOfWeek
SELECT @dayOfWeek [@dayOfWeek],
DATEPART(week, @Thur) [Equals 43],
DATEPART(week, @NextMonday) [Equals 44],
DATEPART(weekday,@Thur) [DATEPART(weekday,@Thur)],
DATEPART(weekday,@NextMonday) [DATEPART(weekday,@NextMonday)]
SET @dayOfWeek = DATEPART(dw, @Thur)
SET DATEFIRST @dayOfWeek
SELECT @dayOfWeek [@dayOfWeek],
DATEPART(week, @Thur) [Equals 43],
DATEPART(week, @NextMonday) [Equals 44],
DATEPART(weekday,@Thur) [DATEPART(weekday,@Thur)],
DATEPART(weekday,@NextMonday) [DATEPART(weekday,@NextMonday)]
You should fix the DATEFIRST before setting it from a date:
DECLARE @dayOfWeek int,
@Thur date= '20191024',
@NextMonday date= '20191028'
SET DATEFIRST 1
SET @dayOfWeek = DATEPART(dw, @Thur)
SET DATEFIRST @dayOfWeek
SELECT @dayOfWeek [@dayOfWeek],
DATEPART(week, @Thur) [Equals 43],
DATEPART(week, @NextMonday) [Equals 44]
October 25, 2019 at 12:40 pm
I'm trying to compare weeks based on the start date of the given week being a thursday. Meaning the week count should count the number of weeks based on a week consisting of Thursday - Wed. However that's not whats happening. I'd expect the last column to be equal to the prev because they are in the same thur-wed week. Am i making sense? The reason is i'm trying to calc employee availability and we have bi weekly rules.
declare @dayOfWeek int
,@Thur date = '2019-10-24'
,@NextMonday date = '10/28/2019'
set @dayOfWeek = datepart(dw,@Thur )
set datefirst @dayOfWeek
select @dayOfWeek
,DATEPART(week,@Thur ) [Equals 43]
,DATEPART(week,@NextMonday) [Equals 44]
I see your description and your code but I still can't figure out what you're trying to do. Are you simply trying to show what the Thursday week start date is for any give date?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 25, 2019 at 12:43 pm
I see your description and your code but I still can't figure out what you're trying to do. Are you simply trying to show what the Thursday week start date is for any give date?
I think the OP is trying to make Thursday the first day of the week.
October 25, 2019 at 1:24 pm
The default for DATEFIRST is 7 (Sunday)
So
set @dayOfWeek = datepart(dw,@Thur)
will set it to 5 when you want it to be 4
This will return 44 for both dates
DECLARE @dayOfWeek int
,@Thur date = '2019-10-24'
,@NextMonday date = '10/28/2019'
SET DATEFIRST 4;
SELECT @@DATEFIRST
,DATEPART(week,@Thur)
,DATEPART(week,@NextMonday)
Far away is close at hand in the images of elsewhere.
Anon.
October 25, 2019 at 3:01 pm
I'd strongly advise against messing with DATEFIRST setting. The code below works under any/all DATEFIRST settings.
declare @date_to_calc_week_of date
set @date_to_calc_week_of = '20191028'
;with cte_date_calcs as (
select dateadd(day, -datediff(day, 3, jan_07) % 7, jan_07) as first_thu_of_year
from ( select datefromparts(year(@date_to_calc_week_of), 1, 7) as jan_07 ) as jan_07
)
select @date_to_calc_week_of as date_to_calc_week_of,
datediff(day, first_thu_of_year, @date_to_calc_week_of) / 7 + 1 as week#
from cte_date_calcs
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".
October 25, 2019 at 8:31 pm
Jeff Moden wrote:I see your description and your code but I still can't figure out what you're trying to do. Are you simply trying to show what the Thursday week start date is for any give date?
I think the OP is trying to make Thursday the first day of the week.
Yep... I get that. But then he also included a Monday in his original example but didn't really do anything with it. What I'm trying to find out is what comparison is he talking about? Is he simply trying to find the date of the Thursday that starts a week for any given date so that he can do aggregations or other "comparisons"? In other words, what's the "end game" being sought here?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 25, 2019 at 8:36 pm
P.S. I also strongly agree with Scott... there's no way that I'd rely on DateFirst settings never mind actually change them.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2019 at 2:03 pm
Jeff Moden wrote:P.S. I also strongly agree with Scott... there's no way that I'd rely on DateFirst settings never mind actually change them.
What is wrong with using DATEFIRST?
Why the strong negativity?
My apologies... that recommendation does deserve an explanation.
I can't speak for Scott but my recommendation (which is more of a Draconian Edict for me) comes from an ages-old "best practice" recommendation to not use it. I've never had a problem with using it simply because I've never used it. The "best practice" is based on the fact that SET DATEFIRST is session scoped but there's also a server-wide configuration setting for the default value of @@DATEFIRST and, yeah... they can be different.
I know... so what, right?
Perhaps I'm wrong in my "feeling" about it but I put "date first" in the same category as default formatting for dates. I don't rely on either of them at the server or session level. I believe that only times I've ever used the likes of (for example) SET DATEFORMAT was in the "forum etiquette" article I wrote or in forum replies that required test data and was not destined to become production code.
So, some of it is based solely on a "Visceral Fear" of "What happens if I use it and something I'm not aware of changes".
The other thing is that such SET statements cannot be used in functions so even if the visceral fear wasn't enough for me to not use it back in SQL Server 6.5 and 7, not being able to use such settings in functions put the proverbial lid on it for me.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2019 at 3:39 pm
David Burrows wrote:Jeff Moden wrote:P.S. I also strongly agree with Scott... there's no way that I'd rely on DateFirst settings never mind actually change them.
What is wrong with using DATEFIRST?
Why the strong negativity?
My apologies... that recommendation does deserve an explanation.
I can't speak for Scott but my recommendation (which is more of a Draconian Edict for me) comes from an ages-old "best practice" recommendation to not use it. I've never had a problem with using it simply because I've never used it. The "best practice" is based on the fact that SET DATEFIRST is session scoped but there's also a server-wide configuration setting for the default value of @@DATEFIRST and, yeah... they can be different.
I know... so what, right?
Perhaps I'm wrong in my "feeling" about it but I put "date first" in the same category as default formatting for dates. I don't rely on either of them at the server or session level. I believe that only times I've ever used the likes of (for example) SET DATEFORMAT was in the "forum etiquette" article I wrote or in forum replies that required test data and was not destined to become production code.
So, some of it is based solely on a "Visceral Fear" of "What happens if I use it and something I'm not aware of changes".
The other thing is that such SET statements cannot be used in functions so even if the visceral fear wasn't enough for me to not use it back in SQL Server 6.5 and 7, not being able to use such settings in functions put the proverbial lid on it for me.
Thanks for that Jeff, understood.
I rarely use DATEFIRST but if I do (for example I want Monday to be first for day of week) then I explicitly use DATEFIRST and to this day never had issues with it.
Yes there are considerations to be made for it's use, it's by no means unique in that, take BULK insert for example, it will only convert US character dates direct to datetime. To convert DD/MM/YYYY format dates I have to use SET DATEFORMAT DMY or a long winded way of importing to temp table as char and fiddle with it to enable conversion.
It seems to ignore the language setting of the login used and IIRC SQL Server's language setting (in server properties) is always English (United States).
Far away is close at hand in the images of elsewhere.
Anon.
October 28, 2019 at 5:44 pm
I do use SET DATEFORMAT as needed. I agree, it's much easier to do that than to try to rewrite a script. Then reset it asap to its original value (yes, I actually do capture the original value and restore it, rather than make any assumptions about what it is/should be).
But I never use SET DATEFIRST. The problem is if you call other code, that code may not work correctly. People assume DATEFIRST is still at its default value.
To be safe and insure accuracy, my code never depends on a specific DATEFIRST setting. E.g., I would never use DATEPART(WEEKDAY, ...). Instead, I use DATEDIFF(DAY, 0, <date_in_question>) % 7. That will always yield 0 for Mon, 1 for Tue, etc., no matter what the DATEFIRST setting is. Once you get used to it, it's actual easier than trying to remember and/or look up what the default date settings are (we have offices around the world so can't too easily assume what the local setting was / is).
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".
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply