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:

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.

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

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:

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

I’m an intl PM in localization and a certified Google Professional Data Engineer. My hobby projects: open source apps (https://github.com/otapi) and DIY stuffs

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store