SET DATEFIRST in FUNCTION
Solution 1
My usual workaround is to use "known-good" dates for my comparisons.
Say, for instance, that I need to check that a date is a saturday. Rather than relying on DATEFIRST
or language settings (for using DATENAME
), I instead say:
DATEPART(weekday,DateToCheck) = DATEPART(weekday,'20120714')
I know that 14th July 2012 was a Saturday, so I've performed the check without relying on any external settings.
The expression (DATEPART(weekday,DateToCheck) + @@DATEFIRST) % 7
will always produce the value 0 for Saturday, 1 for Sunday, 2 for Monday, etc.
So, I'd advise you to create a table:
CREATE TABLE WorkingDays (
NormalisedDay int not null,
DaysInMonth int not null,
WorkingDays int not null
)
Populating this table is a one off exercise. NormalisedDay
would be the value computed by the expression I've given above.
To compute the DaysInMonth
given a particular date, you can use the expression:
DATEDIFF(day,
DATEADD(month,DATEDIFF(month,0,DateToCheck),0),
DATEADD(month,DATEDIFF(month,'20010101',DateToCheck),'20010201'))
Now all your function has to do is look up the value in the table.
(Of course, all of the rows where DaysInMonth
is 28 will have 20 as their result. It's only the rows for 29,30 and 31 which need a little work to produce)
Solution 2
Instead of
SET DATEFIRST 1
You can do
SELECT (DATEPART(weekday, GETDATE()) + @@DATEFIRST - 2) % 7 + 1
Etienne
Updated on June 04, 2022Comments
-
Etienne about 2 years
I want to SET DATEFIRST in my function but it is not allowed.
SET DATEFIRST 1
I can add the code in a SP and call the SP from the function but I am not keen on doing that.
I can SET the DATEFIRST before I call my function but I am not keen on doing that as well.
Any other work around?
EDIT
Below is the code I want to use in my FUNCTION to return the total working days of the month. But I cant add this code into the FUNCTION because of my DATEFIRST
DECLARE @my int DECLARE @myDeduct int DECLARE @day INT DECLARE @mydate DATETIME DECLARE @TotalDays INT SET @mydate = GETDATE() SET @myDeduct = 0 IF (@@DATEFIRST + DATEPART(DW, @mydate)) % 7 not in (0,1) SET DateFirst 1 -- Set it monday=1 (value) --Saturday and Sunday on the first and last day of a month will Deduct 1 IF (DATEPART(weekday,(DATEADD(dd,-(DAY(@mydate)-1),@mydate))) > 5) SET @myDeduct = @myDeduct + 1 IF (DATEPART(weekday,(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)))) > 5) SET @myDeduct = @myDeduct + 1 SET @my = day(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate))) Set @TotalDays = (select (((@my/7) * 5 + (@my%7)) - @myDeduct)) Select @TotalDays
-
Mikael Eriksson almost 12 yearsPerhaps you could rework the logic in your function so you are not dependent on
set datefirst
? -
tenfour almost 12 years
-
Etienne almost 12 yearsPlease see above, I added my code in to explain how I want to use it.
-
-
Etienne almost 12 yearsPlease see above, I added my code in to explain how I want to use it.
-
Damien_The_Unbeliever almost 12 years@Etienne - I've added some useful expressions and a proposed solution.
-
underscore_d over 7 years+1 for
(DATEPART(weekday,DateToCheck) + @@DATEFIRST) % 7
, invaluable! sure beats comparing the weekday names, anyway :O -
ArieKanarie over 6 yearsThis is a good one, nice one liner. Gives you 1 for Monday, 7 for Sunday. Needed this for ISO8601 calculations. See stackoverflow.com/a/41248745/2997016 for a multi line version if modulo is like abracadabra for you
-
underscore_d over 6 yearsIMO this doesn't really the question, as it still depends on the current value of
@@datefirst
. It just 'rotates' the weekdays based on that, so you will only get1
for Monday if the current@@datefirst
is Sunday. That may suffice for some readers, but it's not a comprehensive answer like Kvasi's. As another note, isn't the<= 7
redundant, because there cannot be> 7
weekdays in a week? -
underscore_d over 6 years@ArieKanarie but the answer you linked, unlike this one, doesn't account for the current value of
@@datefirst
, so it only works if that was such that Sunday is weekday1
. Maybe you could post another answer that fixes that. :P -
underscore_d over 6 yearsAnyway, +1, though I used a modified version without the subtraction and addition, since I only need the numbers to be consistent, not specific. So
0
is Saturday,1
is Sunday, etc. This means I (A) can accommodatedatefirst
in a function and (B) without the region-specific, slower kludge of comparing to specificdatename
s instead. -
ArieKanarie over 6 years@underscore_d Yes the
<= 7
could also be a simpleELSE
. At least until we get 8 days a week ;-)