Calculate Number of Working Days Till Current Time in SQL

Let’s say we have a datetime column and want to calculate the number of working days till the current time.

At first spot it seems like an easy thing, as SQL has the DateDiff function. However, it does not exclude the weekends:

The timezone issue and fraction of days

For better monitoring I prefer to see the fraction of days as well. So let’s calculate it to hour precision.

However, there is a big possibility of wrong calculation that we haven’t aligned the timezones. It could cause issues when the TimeStamp datetimes were stored in different timezone than the SQL Server’s timezone — or even the TimeStamp values have several unkown different timezones. The best and easier solution is to use everywhere one timezone — the UTC. This means we need to start storing the TimeStamp values in UTC when we update the table.

If this is all set, we can safely use GETUTCDATE() to getting current date and time:

This is better (and safer) but it still doesn’t exclude the weekends.

Exclude the weekends

Let’s calculate number of weekends between the two dates and subtract from the number of working days:

Remove leading weekend and add trailing working day

To get the precise working day, we need to subtract he first or last day fractions if those are Saturday or Sunday.

If the TimeStamp is on Sunday, the hours between the next midnight and the TimeStamp have to be subtracted:

DATEADD(d,1,DATEDIFF(d,0,TimeStamp)) as nextmidnight,
DATEDIFF(HOUR, TimeStamp, DATEADD(d,1,DATEDIFF(d,0,TimeStamp))) as minusHoursIfSunday

If the current time is on Saturday, the hours between previous midnight and the current time have to be subtracted:

DATEADD(d,0,DATEDIFF(d,0,GETUTCDATE())) as prevmidnight,
DATEDIFF(HOUR, DATEADD(d,0,DATEDIFF(d,0,GETUTCDATE())), GETUTCDATE()) as minusHoursIfSaturday

Adding these cases to the WorkingDays compute:

Final query

Let’s remove all calculation columns and see the cleared up final query for calculation working days with fractions: