Calculate working days - Monday to Fri in Tsql

Question

How can i calculate the last working five days which is monday to Friday. my current script gets the last monday's date, but i cannot get the last friday's date. Please help

declare @StartDate datetime
declare @EndDate datetime


--Calculate date range for report

select @EndDate = Cast(convert(char(10), getdate(), 101)+' 00:00:00' as datetime)
select @StartDate = DateAdd(d, -7, @EndDate)
select @EndDate = Cast(convert(char(10), getdate(), 101)+' 23:59:59' as datetime)

select @StartDate startdate
select @EndDate  enddate

This question is very similar to http://stackoverflow.com/questions/4025047/tsql-function-to-calculate-30-working-days-date-from-a-specified-date-sql-server — Brian Dishaw, Jul 25 '11 at 18:33

score 2 · Answer 1 · answered Jul 25 '11 at 18:30

Datepart offers you an easy way to get the week day:

 SET DATEFIRST 1  -- monday is first day of the week
 SELECT DATEPART(weekday, '20110725')
 -- result is 1

See T-SQL Date functions and SET DATEFIRST for more information.

Using the weekday, you can work out how many days ago last monday and friday are and use 'AddDate' (like you are doing now) to calculate those.

Note that you should really use DATEDIFF for this type of date range selection. If you select everything up to 23:59:59 there's always a chance that some records are left out. For example 23:59:59.001 is out of range but it's still on the same day. With DATEDIFF you can test whether it's on the same day, discarding the time part. No need to bother with casting to string, adding time and casting back.

score 1 · Answer 2 · edited Jan 18 '21 at 12:30

1

The answer is more complex than people are assuming. What you need is to go 5 days back and find the first monday before that, and the first friday after that. You can eather use @@datefirst, a calculation or 'set firstdate 1' for that. I prefer not using the last one, because it can't be done in functions. As you can see i used the calculation, @@datefirst is just as good.

Assuming you want the last group of monday to friday that is in the past. This query will get that. If you trust your current monday, you can just add 5 days and subtract 1 minute (I wouldn't trust it, it only returns last monday if you run the query on a monday).

In my sql, I am not aiming for simplicity, I am aiming for effectivity.

DECLARE @getdate datetime = dateadd(day, cast(getdate() as int), 0)
-- the 'Declare' can also be written like this thanks to @Andriy M
--DECLARE @getdate = CAST(GETDATE() - 0.5 AS int)
SELECT @getdate - 5 - CAST(@getdate- 5 as int) % 7 monday,
dateadd(minute, -1, @getdate) - CAST(@getdate- 5 as int) % 7 friday

Result:

Monday              Friday
2011-07-18 0:00:00  2011-07-22 23:59:00

*first solution was a day off @AndriyM pointed it out, it has been solved.

Answer to @Andriy M

For some reason it acted different than I expected. I can't explain it but try this

select cast(dateadd(day, cast(getdate() as int) - .5, 0) as datetime),
cast(dateadd(day, cast(getdate() as int), 0) as datetime),
cast(dateadd(day, cast(getdate() as int) + .5, 0) as datetime)

in the morning the last 2 fields has same value, in the evening the first 2 fields has the same value. I am as surprised as you are, I wish I could explain it. It was tested here https://data.stackexchange.com/stackoverflow/query/new

edited Jan 18 '21 at 12:30

Community

1
1

answered Jul 25 '11 at 23:12

t-clausen.dk

43,517
12
59
92

You are not using `- 0.5` in `cast(getdate() as int)`, why? You do know that `cast(datetime as int)` *rounds* the datetime rather than truncates it, don't you? – Andriy M Jul 26 '11 at 00:04
What I mean is `cast(cast('20110725 18:00:00' as datetime) as int)` does not return the same as `cast(cast('20110725 06:00:00' as datetime) as int)`. – Andriy M Jul 26 '11 at 00:18
@Andriy M Answer is my script – t-clausen.dk Jul 26 '11 at 04:56
I was indeed surprised when I first learned about that, but, to be honest, it's been months since then. The fact is, as I said above, `cast(datetime as int)` does not merely truncate the time part, but rounds the datetime value to the nearest date, either to the same one (if its time is in the morning), or the next one (if the time is in the afternoon). In other words, `cast(datetime as int)` is equivalent to `cast(round(cast(datetime as float), 0) as int)`. … – Andriy M Jul 26 '11 at 07:22
Your `DECLARE @getdate` statement above can be implemented like this: `DECLARE @getdate = CAST(GETDATE() - 0.5 AS int)` (if you meant to initialise `@getdate` with the date part of `GETDATE()`). I.e., you are subtracting half a day from `datetime`, and if it was in the afternoon, it then becomes in the morning, and CAST produces an `int` equivalent to the same date of `GETDATE()`. And if it was originally in the morning, then after subtracting it becomes in the afternoon of the previous day, and CAST again rounds it to the equivalent of the actual date of `GETDATE()`. … – Andriy M Jul 26 '11 at 07:24
Thus, it works, but depends on the internals of the datetime type. On the other hand, the `DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)` method has often been considered an effecient and independent of the internals way of truncating the time part. – Andriy M Jul 26 '11 at 07:30
@Andriy M thx for your input, I have included your surgestion in the code as a comment. I appriciate it, especially the part that explained that my method is a way of truncating. I was wondering why it did so – t-clausen.dk Jul 26 '11 at 07:36
@AndriyM I am wondering. Is there something wrong with my answers ? Seems like I am not getting any points. Same with my other answers. I never got more than 3 points for an answer. I really thought my answers were better than that. I have seen people getting more than 50 points for answers that were short and simple. Complex solutions rarely get more than 1 point. – t-clausen.dk Jul 26 '11 at 08:12
@AndriyM let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/1829/discussion-between-t-clausen-dk-and-andriy-m) – t-clausen.dk Jul 26 '11 at 08:12

score 0 · Answer 3 · answered Sep 18 '12 at 09:55

DECLARE @my int
DECLARE @myDeduct int
DECLARE @day INT
DECLARE @mydate DATETIME

SET @mydate = '2012-08-01'

SET @myDeduct = 0
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)))

select (((@my/7) * 5 + (@my%7)) - @myDeduct) as Working_Day_per_month

While this does produce an interesting result that someone out there is interested to know, it actually does not answer the question that is being asked above. Take another look above.. ;-) — Rob.Kachmar, Jan 23 '15 at 22:56

Rob.Kachmar · Answer 4 · 2015-01-23T23:43:02.900

I'll throw my hat in the ring too. :-)

DECLARE @Date      datetime = '01/11/2015'
DECLARE @StartDate datetime = DATEADD(d, (1 - (DATEDIFF(d, CAST('1899.12.31' AS datetime), @Date - 6) % 7)), @Date - 6) -- MONDAY
DECLARE @EndDate   datetime = DATEADD(d, (5 - (DATEDIFF(d, CAST('1899.12.31' AS datetime), @Date - 6) % 7)), @Date - 6) -- FRIDAY

      SELECT '@Date'      as Variable ,CONVERT(date, @Date)      as DateValue ,DATENAME(dw, @Date)      as DayOfTheWeek
UNION SELECT '@StartDate' as Variable ,CONVERT(date, @StartDate) as DateValue ,DATENAME(dw, @StartDate) as DayOfTheWeek
UNION SELECT '@EndDate'   as Variable ,CONVERT(date, @EndDate)   as DateValue ,DATENAME(dw, @EndDate)   as DayOfTheWeek

-- Variable    DateValue   DayOfTheWeek
-- ----------  ----------  ------------
-- @Date       2015-01-11  Sunday
-- @StartDate  2015-01-05  Monday
-- @EndDate    2015-01-09  Friday

BONUS: Here you can generate a quick table of the 5 weekdays using the same technique.

SELECT DATENAME(dw, DATEADD(d, TT.DaysToAdd, DATEADD(d, (1 - (DATEDIFF(d, CAST('1899.12.31' AS datetime), @Date - 6) % 7)), @Date - 6))) as DayOfTheWeek
      ,             DATEADD(d, TT.DaysToAdd, DATEADD(d, (1 - (DATEDIFF(d, CAST('1899.12.31' AS datetime), @Date - 6) % 7)), @Date - 6))  as DateValue
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 as DaysToAdd FROM (VALUES(0),(0),(0),(0),(0)) a(n)) as TT

-- DayOfTheWeek                   DateValue
-- ------------------------------ -----------------------
-- Monday                         2015-01-05 00:00:00.000
-- Tuesday                        2015-01-06 00:00:00.000
-- Wednesday                      2015-01-07 00:00:00.000
-- Thursday                       2015-01-08 00:00:00.000
-- Friday                         2015-01-09 00:00:00.000

Here's an explanation:

1) First we need to know what date to begin our evaluation from. For this example, we chose to use Sunday, January 11th, 2015.

DECLARE @Date2 datetime = '01/11/2015'

1b) Here's a nice to have bonus technique of getting the name for the day of the week, given a date value

SELECT @Date2 as DateValue, DATENAME(dw, @Date2) as DayOfTheWeek

2) Next we need to know deterministically (based on the US calendar) what the given day of the week is numerically between 1 and 7

NOTE: 1899.12.31 is the first Sunday before 1900.01.01, which is the MINIMUM value for the SmallDateTime data type.
NOTE: Yes, you could use DATEPART(dw, @Date) like this more simply, but it is not deterministic given that certain server environments could have different configurations
RESULTS: 1 = Sunday | 2 = Monday | 3 = Tuesday | 4 = Wednesday | 5 = Thursday | 6 = Friday | 7 = Saturday
```
SELECT ((DATEDIFF(d, CAST('1899.12.31' AS datetime), @Date2) % 7) + 1) [DayOfWeek Deterministic (Based on US)]
```

3) Now, given any date, you should have a deterministic way of determining the Monday for that given week

SELECT DATEADD(d, (1 - (DATEDIFF(d, CAST('1899.12.31' AS datetime), @Date2) % 7)), @Date2) as [Monday Day of the Week - Deterministic (Based on US)]

4) Now, given any date, you should have a deterministic way of determining the Friday for that given week

SELECT DATEADD(d, (5 - (DATEDIFF(d, CAST('1899.12.31' AS datetime), @Date2) % 7)), @Date2) as [Monday Day of the Week - Deterministic (Based on US)]

5) The last date manipulation technique we need is to know how to get into a week that has the first full week of weekdays happening before it. For instance, if we are on a Sunday and we subtract 1 day from it, then we get to Saturday, which puts us in the previous week, which is the first full week of weekdays. Alternatively, if we also subtracted 1 day from Monday, it would only get us to Sunday, which is not the previous week, so subtracting 1 day is not enough. On the flip side, if we were on a Saturday and subtracted 7 days, it would take us past the previous full week of weekday, into the week before it, which is too far. Here's a run down of the analysis to figure out what the magic numbers is that you can subtract by that will work with any day of the week. As you can see below, the magic number is 6.

--                                            DAYS TO SUBTRACT
-- Day of the Week      - 0     - 1     - 2     - 3     - 4     - 5     - 6     - 7
-- ===============      ====    ====    ====    ====    ====    ====    ====    ====
-- Sunday               Bad     Good    Good    Good    Good    Good    Good    Good
-- Monday               Bad     Bad     Good    Good    Good    Good    Good    Good
-- Tuesday              Bad     Bad     Bad     Good    Good    Good    Good    Good
-- Wednesday            Bad     Bad     Bad     Bad     Good    Good    Good    Good
-- Thursday             Bad     Bad     Bad     Bad     Bad     Good    Good    Good
-- Friday               Bad     Bad     Bad     Bad     Bad     Bad     Good    Good
-- Saturday             Good    Good    Good    Good    Good    Good    Good    Bad

BONUS) If you want to have all the weekdays in little table, then you would want to also use a quick zero based "tally table". There are many ways to do this, so pick your flavor. Here are few.

SELECT * FROM (SELECT 0 as DaysToAdd UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) as TT
SELECT * FROM (SELECT TOP 5 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 as DaysToAdd FROM sys.all_columns a CROSS JOIN sys.all_columns b) as TT
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 as DaysToAdd FROM (VALUES(0),(0),(0),(0),(0)) a(n)) as TT

score 0 · Answer 5 · edited May 23 '17 at 12:04

0

These two questions:

might be of some help.

Although, if you already know how to find the last Monday, you can easily find the corresponding Friday by adding 4 days to the Monday date using the DATEADD() function. For example:

SELECT @EndDate = DATEADD(DAY, 4, @StartDate)

edited May 23 '17 at 12:04

Community

1
1

answered Jul 25 '11 at 19:51

Andriy M

76,112
17
94
154

score 0 · Answer 6 · answered Jul 25 '11 at 20:35

It's all relative to TODAY's date, right?

So find out what today is DATEPART(weekday, getdate())

And then turn that into an adjustment variable -- e.g.:

declare @adjustment int
set @adjustment = CASE DATEPART(weekday, getdate()) WHEN 'MONDAY' THEN 0 WHEN 'TUESDAY' THEN 1, ...END

So then the Monday you want would be today - 7 - @adjustment
...and the Friday you want would be Monday + 5

Declare @myMonday smalldatetime,
        @myFriday smalldatetime
set @myMonday = getdate() - 7 - @adjustment
set @myFriday = @myMonday + 5

Calculate working days - Monday to Fri in Tsql

6 Answers6