Like any other enterprise RDBMS system, SQL Server ships with several built-in functions that make developers’ T-SQL code clean, convenient and reusable. To demonstrate the efficiency of functions, say we needed to retrieve a server name for one of our SQL Server instances. Well, one of doing this would be to write a SELECT statement that would query the system view [sys].[servers] from the master database as shown in Script 1.
1 2 3 4 5 6 7 8 | USE master; GO SELECT [name] AS [Server Name] FROM [sys].[servers]; GO |
However, another simpler and cleaner approach is for me to simply call on the @@SERVERNAME built-in function shown in Script 2
1 2 3 4 | SELECT @@SERVERNAME AS [Server Name]; GO |
Unfortunately, it is impossible for SQL Server to provide built-in functions for everything that developers would need. That is why – as developers – we are given an ability to create our own user-defined functions. One such common user-defined function involves the ability to calculate the total number of working days and working hours within a given date range. I have personally noticed a need for such a function in cases whereby some KPIs relates to determining a total number of days/time it takes to resolve a customer complaint. In this article, we take a look at some of the tricks and T-SQL methods that can be used to easily create a user-defined function that calculates working days and hours.
Calculate Working Days using DATEDIFF
In this approach, we employ several steps that make use of DATEDIFF and DATEPART functions to successfully determine working days.
Step 1: Calculate the total number of days between a date range
In this step, we use the DAY interval within the DATEDIFF function to determine the number of days between two dates. The output of this calculation is stored in the @TotDays variable, as shown in Script 3.
1 2 3 4 5 6 7 8 | DECLARE @DateFrom DATETIME; DECLARE @DateTo DATETIME; SET @DateFrom = '2017-06-03 11:19:11.287'; SET @DateTo = '2017-06-11 13:53:14.750'; DECLARE @TotDays INT= DATEDIFF(DAY, @DateFrom, @DateTo) |
Following the execution of Script 3, the value of the @TotDays variable is 8 days as shown in Figure 1.
The total of 8 days is actually incorrect as it is excluding the start date. Say for instance that for some reason you ended up working on the 25th of December 2016 – a Christmas day. If you were to perform a working day calculation in SQL Server using the DATEDIFF function as shown in Script 4, you would get an incorrect result of 0 total days as shown in Figure 2.
1 2 3 4 5 | SET @DateFrom = '2016-12-25'; SET @DateTo = '2016-12-25'; DECLARE @TotDays INT= DATEDIFF(DAY, @DateFrom, @DateTo) AS [TotalDays]; |
One way to get around this issue is to always increment the output of a DATEDIFF function by 1, as shown in Script 5.
1 2 3 | DECLARE @TotDays INT= DATEDIFF(DAY, @DateFrom, @DateTo) + 1 AS [TotalDays]; |
Following the increment by 1, the total number of days shown in Figure 1 changes from 8 to 9 as shown in Figure 3.
Step 2: Calculate the total number of weeks between a date range
Once we have obtained the total number of days, we now need to:
- Calculate the total number of weeks between two dates, and then
- Subtracts those number of weeks from the total number of days
In order to do this calculation, we again use the DATEDIFF function but this time we change the interval to week (represented as WEEK or WK). The output of the week calculation is stored in the @TotWeeks variable, as shown in Script 6.
1 2 3 | DECLARE @TotWeeks INT= DATEDIFF(WEEK, @DateFrom, @DateTo); |
Given the date range specified in Script 3, our week calculation returns 2 as shown in Figure 4.
Again, just as in the calculation of days, the output of the week calculation – 2 weeks – is incorrect. This time the issue is as a result of the way that WEEK interval works within the DATEDIFF function. The WEEK interval in DATEDIFF does not actually calculate the number of weeks, instead it calculates the number of instances that a complete weekend appears (combination of Saturday and Sunday) within the specified date range. Consequently, for a more accurate week calculation, we should always multiply the output by 2 – the number of days in a weekend. The revised script for calculating the number of weeks is shown below in Script 7.
1 2 3 | DECLARE @TotWeeks INT= (DATEDIFF(WEEK, @DateFrom, @DateTo) * 2) AS [TotalWeeks]; |
The output of Script 7 basically doubles what was returned in Figure 4 from 2 to 4 weeks as shown in Figure 5.
Step 3: Exclude Incomplete Weekends
The final steps involve the exclusion of incomplete weekend days from being counted as part of working days. Incomplete weekend days refer to instances whereby the Date From parameter value falls on a Sunday or the Date To parameter value is on a Saturday. The exclusion of incomplete weekends can be done by either using DATENAME or DATEPART functions. Whenever you can, refrain from using the DATEPART in calculating working days as it is affected by your language settings of your SQL Server instance. For instance, Script 8 returns Sunday as day name for both US and British language settings.
1 2 3 4 5 6 7 | SET LANGUAGE us_english; SELECT DATENAME(weekday, '20170611') [US]; SET LANGUAGE British; SELECT DATENAME(weekday, '20170611') [British]; |
However, when DATEPART function is used as shown in Script 9, we get different values for US and British settings.
1 2 3 4 5 6 7 | SET LANGUAGE us_english; SELECT DATEPART(weekday, '20170611') [US]; SET LANGUAGE British; SELECT DATEPART(weekday, '20170611') [British]; |
Script 10 shows the complete definition for a user defined function that calculates working days by mostly using the DATEDIFF function.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | CREATE FUNCTION [dbo].[fn_GetTotalWorkingDays] ( @DateFrom Date, @DateTo Date ) RETURNS INT AS BEGIN DECLARE @TotDays INT= DATEDIFF(DAY, @DateFrom, @DateTo) + 1; DECLARE @TotWeeks INT= DATEDIFF(WEEK, @DateFrom, @DateTo) * 2; DECLARE @IsSunday INT= CASE WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday' THEN 1 ELSE 0 END; DECLARE @IsSaturday INT= CASE WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday' THEN 1 ELSE 0 END; DECLARE @TotWorkingDays INT= @TotDays - @TotWeeks - @IsSunday + @IsSaturday; RETURN @TotWorkingDays; END |
Now if we call this function as shown in Script 11, 5 is returned in Figure 8 – which is actually the correct number of working days between the 3rd and 11th of June 2017.
1 2 3 | SELECT [dbo].[fn_GetTotalWorkingDays] ('2017-06-03','2017-06-11') |
Calculate Working Days using WHILE Loop
Another approach to calculating working days is to use a WHILE loop which basically iterates through a date range and increment it by 1 whenever days are found to be within Monday – Friday. The complete script for calculating working days using the WHILE loop is shown in Script 12.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | CREATE FUNCTION [dbo].[fn_GetTotalWorkingDaysUsingLoop] (@DateFrom DATE, @DateTo DATE ) RETURNS INT AS BEGIN DECLARE @TotWorkingDays INT= 0; WHILE @DateFrom <= @DateTo BEGIN IF DATENAME(WEEKDAY, @DateFrom) IN('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday') BEGIN SET @TotWorkingDays = @TotWorkingDays + 1; END; SET @DateFrom = DATEADD(DAY, 1, @DateFrom); END; RETURN @TotWorkingDays; END; GO |
Although the WHILE loop option is cleaner and uses less lines of code, it has the potential of being a performance bottleneck in your environment particularly when your date range spans across several years.
Calculate Working Hours
The final section of this article involves the calculation of working hours based on a given date range.
Step 1: Calculate total working days
In this step, we use a similar approach to the previous sections whereby we calculate the total working days. The only difference is that we are not incrementing the output by 1 as shown in Script 13.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2), @DateFrom DATETIME, @DateTo DATETIME; SET @DateFrom = '2017-06-05 11:19:11.287'; SET @DateTo = '2017-06-07 09:53:14.750'; SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo) -(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2) -CASE WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday' THEN 1 ELSE 0 END+CASE WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday' THEN 1 ELSE 0 END; |
Step 2: Calculate total number of seconds
The next part involves getting a difference in seconds between the two dates and converting that difference into hours by dividing by 3600.0 as shown in Script 14.
1 2 3 4 5 6 7 8 9 10 11 12 | SET @TotalTimeDiff = ( SELECT DATEDIFF(SECOND, ( SELECT CONVERT(TIME, @DateFrom) ), ( SELECT CONVERT(TIME, @DateTo) )) / 3600.0 ); |
The last part involves multiplying the output of Step 1 by 24 (total number of hours in a day) and then later adding that to the output of Step 2 as shown in Script 15.
1 2 3 | SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff; |
Finally, the complete script that can be used to create a user defined function for calculating working hours is shown in Script 16 and its application is shown in Figure 9.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | CREATE FUNCTION [dbo].[fn_GetTotalWorkingHours] ( @DateFrom Datetime, @DateTo Datetime ) RETURNS DECIMAL(18,2) AS BEGIN DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2) SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo) -(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2) -CASE WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday' THEN 1 ELSE 0 END+CASE WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday' THEN 1 ELSE 0 END; SET @TotalTimeDiff = ( SELECT DATEDIFF(SECOND, ( SELECT CONVERT(TIME, @DateFrom) ), ( SELECT CONVERT(TIME, @DateTo) )) / 3600.0 ); RETURN(SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff) END GO |
- Author
- Recent Posts
Sifiso Ndlovu
Sifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.
View all posts by Sifiso W. Ndlovu
Latest posts by Sifiso Ndlovu (see all)
- Dynamic column mapping in SSIS: SqlBulkCopy class vs Data Flow - February 14, 2020
- Monitor batch statements of the Get Data feature in Power BI using SQL Server extended events - July 1, 2019
- Bulk-Model Migration in SQL Server Master Data Services - May 30, 2019
Related posts:
- Calculate SQL Percentile using the PERCENT_RANK function in SQL Server
- An MDS Driven Approach to a Turnaround Time Calculation in SQL Server
- DATEADD SQL function introduction and overview
- How to work with Visual Studio and VM databases in Azure
- FAQ about Dates in SQL Server
FAQs
How do I calculate hours worked in SQL? ›
- SELECT Product_Name,Operater_Name.
- ,DATEDIFF(HH,MIN(CASE WHEN Old_Status='NEW' THEN Created END),MAX(CASE WHEN New_Status='Completed' THEN Created END))AS Total_Hours.
- ,DATEDIFF(HH,MIN(CASE WHEN Old_Status='WIP' THEN Created END),MAX(CASE WHEN New_Status='WIP' THEN Created END))AS Wait_Hours.
You Can simply use datediff function of sql. and then you can subtract weekends between those dates if any. For example check below query. And If You want to exclude holiday's too, then, You also can calculate holidays between start/end date and can subtract that from final selection.
How can I get business days in SQL? ›SQL Server WORKDAYS function. Use WORKDAY to calculate the date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. The weekend days are always assumed to be Saturday and Sunday.
How do you calculate hours of work? ›- Step 1: Determine start and end time. Simple as that---record what time you start and what time you end. ...
- Step 2: Convert time to military time. ...
- Step 3: Subtract start time from end time. ...
- Step 4: Subtract unpaid breaks. ...
- Step 5: Convert to decimal format. ...
- Step 6: Add up total hours for pay period.
To calculate the difference between the arrival and the departure in T-SQL, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument can be microsecond , second , minute , hour , day , week , month , quarter , or year . Here, you'd like to get the difference in seconds, so choose second.
Is there a day function in SQL? ›If you want to get a day from a date in a table, use the SQL Server DAY() function. This function takes only one argument – the date. This can be a date or date and time data type. (In our example, the column VisitDate is of the date data type.)
What is a datediff () function? ›You can use the DateDiff function to determine how many specified time intervals exist between two dates. For example, you might use DateDiff to calculate the number of days between two dates, or the number of weeks between today and the end of the year.
How do I calculate working days in a month in SQL? ›You Can simply use datediff function of sql. and then you can subtract weekends between those dates if any. For example check below query. And If You want to exclude holiday's too, then, You also can calculate holidays between start/end date and can subtract that from final selection.
How can I calculate days between two dates in SQL? ›The DATEDIFF() function returns the difference between two dates.
How does SQL calculate Saturday and Sunday? ›Here @@DATEFIRST is used to set the first day of the week to a number from 1 through 7. In the above query we are takingthe date part of CheckedInDate and will add @@DATEFIRST value. Further the value will be divided by 7 then we will get day particular day of the week based on 0 or 1 etc.
How do I find Saturday and Sunday in SQL Server? ›
The day of the week can be retrieved in SQL Server by using the DatePart function. The value returned by function is between 1 (Sunday) and 7 (Saturday). To convert this to a string representing the day of the week, use a CASE statement. Method 1: Create function running following…
What is the datatype for day in SQL? ›| Data type | Format | Accuracy |
|---|---|---|
| date | YYYY-MM-DD | 1 day |
| smalldatetime | YYYY-MM-DD hh:mm:ss | 1 minute |
| datetime | YYYY-MM-DD hh:mm:ss[.nnn] | 0.00333 second |
| datetime2 | YYYY-MM-DD hh:mm:ss[.nnnnnnn] | 100 nanoseconds |
SQL Server comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD. DATETIME - format: YYYY-MM-DD HH:MI:SS. SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS. TIMESTAMP - format: a unique number.
How do I calculate my total working days? ›The number of work days in 2022 is calculated by adding up all the weekdays (Mon-Fri) in 2022 and subtracting the 11 public federal holidays that fall on a weekday in 2022. The number of work days in a given year varies depending on what day of the week the year starts on.
How do you calculate your working day? ›To calculate the number of working days between two dates, we must follow these steps: Count the number of days between the two dates. Subtract the number of weekend days between the two dates.
How do you calculate work per day? ›- Get the hours per week =Hours per day x Working days(per week)
- Get the hours in a year = Hours per Week x 52 weeks (in a year)
- Get the hours per months = Hours in Year ÷ 12 (months)
- Get Hourly Pay = Monthly Salary ÷ Hours Per Month.
- Get Daily Pay = Hourly Pay x Hours Per Day.
- Declare @Date_2 DATETIME = '2020-04-30 10:01:10.022'
- Declare @Date_1 DATETIME = '2020-04-30 10:00:00.000'
- Select CONVERT (TIME, @Date_2 - @Date_1) as Elapsed_Time.
The TIME() function extracts the time part from a given time/datetime. Note: This function returns "00:00:00" if expression is not a datetime/time, or NULL if expression is NULL.
What is date and time function in SQL? ›This function is used to determine the number of days difference between two days. For example, SELECT DATEDIFF(month, '2020-12-31 23:59:59', '2022-01-01 00:00:00'); -- outputs: 13. Here, the function returns the difference between two dates in months. DATEADD(date_part, number, date)
Does today () update daily? ›The value returned by the TODAY function updates automatically, so the below formula is useful if you want the worksheet to always display the current date, regardless of when you open it.
What does Getdate () mean? ›
The GETDATE() function returns the current database system date and time, in a 'YYYY-MM-DD hh:mm:ss.mmm' format.
Is hour a function in SQL? ›HOUR() function is a date/time function in standard query language (SQL) that is used to extract the hour part from a given datetime or timestamp data. This function is primarily supported in databases such as MYSQL and ORACLE.
How do you compare two dates and time? ›...
It returns an integer value,
- <0 − If date1 is earlier than date2.
- 0 − If date1 is the same as date2.
- >0 − If date1 is later than date2.
If startdate and enddate have different year values, but they have the same calendar week values, DATEDIFF will return 0 for datepart week.
What is the use of ADD_MONTHS in SQL? ›The ADD_MONTHS function takes a DATETIME or DATE expression as its first argument, and requires a second integer argument, specifying the number of months to add to the first argument value. The second argument can be positive or negative.
How do I get Sundays in SQL? ›Option 1: Sunday as the First Day of the Week
DATEADD(week, DATEDIFF(week, -1, RegistrationDate), -1) AS Sunday; The function DATEADD() takes three arguments: a datepart, a number, and a date.
- Just realised, this is written in T-Sql (Sql Server), if the answer is needed for MySql then something like: SELECT DATE_ADD(NOW(), INTERVAL -30 DAY) is the equivalent. – amelvin. May 14, 2010 at 10:05.
- Just add this to your answer:) – hgulyan. ...
- In MySQL, DATE_SUB(NOW(), INTERVAL 30 DAY) works as well. – radtek.
- DATEDIFF(day, 0, current_timestamp) will give the number of days since 1900-01-01 (which importantly was a Monday)
- FLOOR(DATEDIFF(day, 0, current_timestamp)/7.0) Converts days since 1900–01–01 into weeks and rounds the output down.
In SQL Server 2012 and later version, we can get the total number of days in a month using EOMONTH() function. This method is the easiest way to get the total number of days in a month.
How do I count the number of Sundays between two dates in SQL? ›Here's one way: SELECT early_date , late_date , ( TRUNC (late_date + 1, 'IW') - TRUNC (early_date, 'IW') ) / 7 AS sundays FROM table_x ; This does not depend on your NLS settings.
What does Sp_help do in SQL? ›
Reports information about a database object (any object listed in the sys. sysobjects compatibility view), a user-defined data type, or a data type.
Which datatype is used for time? ›The TIMESTAMP data type consists of a date and time, with optional time zone.
What is the date time format in SQL? ›SQL Date Time Format Data Types
The following types of data are available in SQL Server for storing Date or date/time values in the database: DATE - format: YYYY-MM-DD. DATETIME - format: YYYY-MM-DD HH:MI:SS. TIMESTAMP - format: YYYY-MM-DD HH:MI:SS.
Numeric data types are normally used to store data like price, salary etc.
How can I get date in DD MMM YYYY format in SQL? ›- Use the FORMAT function to format the date and time data types from a date column (date, datetime, datetime2, smalldatetime, datetimeoffset, etc. ...
- To get DD/MM/YYYY use SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date.
Time is the SQL Server data type that we use to store Time. It stores the time of a day, without time zone and using 24 hours format.
What is To_date in SQL? ›The TO_DATE function converts date strings in various formats to a date integer value, with data type DATE. It is used to input dates in various string formats, storing them in a standard internal representation. TO_DATE returns a date with the following format: nnnnn.
How do I calculate hours and minutes in SQL? ›1 Answer. We can be use CONVERT () function to calculate the time difference between two timestamps in hours and minutes.
How do you calculate total working hours by each employee from their start date in SQL? ›You can use lead() and then aggregate: select employeeid, sum(timestampdiff(hour, created, next_created) as num_hours from (select t.
How do you sum time in SQL? ›- SELECT TOP 2*, ISNULL((RIGHT('00' + CONVERT(VARCHAR(10), SUM(DATEDIFF(MINUTE, FromTime, ToTime)) / 60), 2)
- + ':' + RIGHT('00' + CONVERT(VARCHAR(2), SUM(DATEDIFF(Minute, FromTime, ToTime)) % 60), 2)
- + ':' + RIGHT('00' + CONVERT(VARCHAR(2), SUM(DATEDIFF(SECOND, FromTime, ToTime)) % 60), 2)), 0)
How do you sum HH mm in SQL? ›
- CONVERT (VARCHAR(20),SUM (DATEDIFF(Minute,DispatchTime, ClearTime))/60)+'.'+
- RIGHT('00'+ CONVERT(VARCHAR(2), SUM (DATEDIFF(Minute,DispatchTime, ClearTime)) % 60),2) AS Utilization3.
- CURRENT_TIMESTAMP - returns the date and time of the machine the SQL Server is running on.
- GETDATE() - returns the date and time of the machine the SQL Server is running on.
- declare @temp table.
- (
- groupid int,
- [hour] datetime.
- )
- insert into @temp values.
- (1,'2020-01-01 04:38:00'),
- (1,'2020-01-01 00:25:00'),
SQL Date Data Types
DATETIME - format: YYYY-MM-DD HH:MI:SS. TIMESTAMP - format: YYYY-MM-DD HH:MI:SS. YEAR - format YYYY or YY.
We can use DATEPART() function to get the HOUR part of the DateTime in Sql Server, here we need to specify datepart parameter of the DATEPART function as hour or hh.
How do I sum two time columns in SQL? ›...
Table script:
- Convert Timefield to datetime.
- Convert hours , minutes to seconds in the converted datetime.
- Sum the seconds.
- Convert seconds back to hour format.
You can use the string expression argument in an SQL aggregate function to perform a calculation on values in a field. For example, you could calculate a percentage (such as a surcharge or sales tax) by multiplying a field value by a fraction.