How to calculate work days and hours in SQL Server (2023)

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

Script 1

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

Script 2

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)

Script 3

Following the execution of Script 3, the value of the @TotDays variable is 8 days as shown in Figure 1.

How to calculate work days and hours in SQL Server (1)

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];

(Video) SQL Query | Calculate number of weekdays between two dates | Exclude Weekends | DateDiff | DateName
Script 4

How to calculate work days and hours in SQL Server (2)

Figure 2

One way to get around this issue is to always increment the output of a DATEDIFF function by 1, as shown in Script 5.

Script 5

Following the increment by 1, the total number of days shown in Figure 1 changes from 8 to 9 as shown in Figure 3.

How to calculate work days and hours in SQL Server (3)

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);

Script 6

Given the date range specified in Script 3, our week calculation returns 2 as shown in Figure 4.

How to calculate work days and hours in SQL Server (4)

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];

Script 7

The output of Script 7 basically doubles what was returned in Figure 4 from 2 to 4 weeks as shown in Figure 5.

(Video) T-SQL: How to calculate the total number of working days between two dates?

How to calculate work days and hours in SQL Server (5)

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];

Script 8

How to calculate work days and hours in SQL Server (6)

Figure 6

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 9

How to calculate work days and hours in SQL Server (7)

Figure 7

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

Script 10

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')

Script 11

How to calculate work days and hours in SQL Server (8)

(Video) Calculate Employees' Total Working hours using SQL Server

Figure 8

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

Script 12

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;

Script 13

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

);

Script 14

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;

Script 15

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

(Video) Working hours without Holidays, weekends and by country in power query
Script 16

How to calculate work days and hours in SQL Server (9)

Figure 9

  • 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.

Sifiso's LinkedIn profile

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
(Video) Solving a REAL Business Use Case Using SQL | Business Days Excluding Weekends and Public Holidays

Related posts:

  1. Calculate SQL Percentile using the PERCENT_RANK function in SQL Server
  2. An MDS Driven Approach to a Turnaround Time Calculation in SQL Server
  3. DATEADD SQL function introduction and overview
  4. How to work with Visual Studio and VM databases in Azure
  5. FAQ about Dates in SQL Server

FAQs

How do I calculate hours worked in SQL? ›

Try this query:
  1. SELECT Product_Name,Operater_Name.
  2. ,DATEDIFF(HH,MIN(CASE WHEN Old_Status='NEW' THEN Created END),MAX(CASE WHEN New_Status='Completed' THEN Created END))AS Total_Hours.
  3. ,DATEDIFF(HH,MIN(CASE WHEN Old_Status='WIP' THEN Created END),MAX(CASE WHEN New_Status='WIP' THEN Created END))AS Wait_Hours.
23 Aug 2022

How do you calculate working days in SQL excluding weekends and holidays? ›

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? ›

How to calculate hours worked
  1. Step 1: Determine start and end time. Simple as that---record what time you start and what time you end. ...
  2. Step 2: Convert time to military time. ...
  3. Step 3: Subtract start time from end time. ...
  4. Step 4: Subtract unpaid breaks. ...
  5. Step 5: Convert to decimal format. ...
  6. Step 6: Add up total hours for pay period.

How does SQL Server calculate time? ›

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? ›

Date and time data types
Data typeFormatAccuracy
dateYYYY-MM-DD1 day
smalldatetimeYYYY-MM-DD hh:mm:ss1 minute
datetimeYYYY-MM-DD hh:mm:ss[.nnn]0.00333 second
datetime2YYYY-MM-DD hh:mm:ss[.nnnnnnn]100 nanoseconds
2 more rows
29 Sept 2022

What is date command in SQL? ›

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? ›

Hourly, Daily Rate Calculator
  1. Get the hours per week =Hours per day x Working days(per week)
  2. Get the hours in a year = Hours per Week x 52 weeks (in a year)
  3. Get the hours per months = Hours in Year ÷ 12 (months)
  4. Get Hourly Pay = Monthly Salary ÷ Hours Per Month.
  5. Get Daily Pay = Hourly Pay x Hours Per Day.

How does SQL Server calculate hours and minutes? ›

1 Answer
  1. Declare @Date_2 DATETIME = '2020-04-30 10:01:10.022'
  2. Declare @Date_1 DATETIME = '2020-04-30 10:00:00.000'
  3. Select CONVERT (TIME, @Date_2 - @Date_1) as Elapsed_Time.
30 Apr 2020

What is time function SQL? ›

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? ›

Compare() method in C# is used for comparison of two DateTime instances.
...
It returns an integer value,
  1. <0 − If date1 is earlier than date2.
  2. 0 − If date1 is the same as date2.
  3. >0 − If date1 is later than date2.
11 Nov 2019

What is 0 in datediff in SQL? ›

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.

How do I get 30 days in SQL? ›

  1. 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.
  2. Just add this to your answer:) – hgulyan. ...
  3. In MySQL, DATE_SUB(NOW(), INTERVAL 30 DAY) works as well. – radtek.

How do I get Monday of every week in SQL? ›

SQL Server — Find Monday of the Current Week
  1. DATEDIFF(day, 0, current_timestamp) will give the number of days since 1900-01-01 (which importantly was a Monday)
  2. FLOOR(DATEDIFF(day, 0, current_timestamp)/7.0) Converts days since 1900–01–01 into weeks and rounds the output down.

How do I calculate the number of days in a month in SQL Server? ›

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.

What data type is salary in SQL? ›

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? ›

SQL Date Format with the FORMAT function
  1. Use the FORMAT function to format the date and time data types from a date column (date, datetime, datetime2, smalldatetime, datetimeoffset, etc. ...
  2. To get DD/MM/YYYY use SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date.
13 Oct 2021

Is time a data type in SQL? ›

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? ›

Sum Time in Sql
  1. SELECT TOP 2*, ISNULL((RIGHT('00' + CONVERT(VARCHAR(10), SUM(DATEDIFF(MINUTE, FromTime, ToTime)) / 60), 2)
  2. + ':' + RIGHT('00' + CONVERT(VARCHAR(2), SUM(DATEDIFF(Minute, FromTime, ToTime)) % 60), 2)
  3. + ':' + RIGHT('00' + CONVERT(VARCHAR(2), SUM(DATEDIFF(SECOND, FromTime, ToTime)) % 60), 2)), 0)
4 Sept 2016

How do you sum HH mm in SQL? ›

Find Sum of time without DATEDIFF format as HH.MM
  1. CONVERT (VARCHAR(20),SUM (DATEDIFF(Minute,DispatchTime, ClearTime))/60)+'.'+
  2. RIGHT('00'+ CONVERT(VARCHAR(2), SUM (DATEDIFF(Minute,DispatchTime, ClearTime)) % 60),2) AS Utilization3.
24 Jan 2022

How can calculate date and time in SQL? ›

SQL Server CURRENT_TIMESTAMP, GETDATE() and GETUTCDATE() Functions
  1. CURRENT_TIMESTAMP - returns the date and time of the machine the SQL Server is running on.
  2. GETDATE() - returns the date and time of the machine the SQL Server is running on.
17 May 2021

How do I sum hours in SQL Server? ›

Please refer below and check whether it is helpful to you.
  1. declare @temp table.
  2. (
  3. groupid int,
  4. [hour] datetime.
  5. )
  6. insert into @temp values.
  7. (1,'2020-01-01 04:38:00'),
  8. (1,'2020-01-01 00:25:00'),
26 Nov 2020

What is the syntax for time in SQL? ›

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.

How do I get HH from datetime in SQL? ›

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? ›

Now we need to sum the TimeField column in the above table. We are going to do it in single query but there are 4 steps inside that.
...
Table script:
  1. Convert Timefield to datetime.
  2. Convert hours , minutes to seconds in the converted datetime.
  3. Sum the seconds.
  4. Convert seconds back to hour format.
13 Jun 2012

Can you do calculations in SQL? ›

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.

Videos

1. Sql Server - Create an Add_Work_Days function
(Bado-Dev)
2. How to add or subtract days or time in SQL Server using the DATEADD Function
(SQL Server 101)
3. SIMPLY Calculate Working Days in Power BI | DAX NETWORKDAYS Function
(Matador Software)
4. Calculating the first day of the week in SQL Server
(SQL Server 101)
5. SQL Basics Part-7 Calculate the Difference between dates, Date Formats
(SQL Learn & Share)
6. How to Subtract Dates in SQL Server (Years, Days, Hours, Seconds)
(Coding With Brett)
Top Articles
Latest Posts
Article information

Author: Frankie Dare

Last Updated: 01/26/2023

Views: 6570

Rating: 4.2 / 5 (53 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Frankie Dare

Birthday: 2000-01-27

Address: Suite 313 45115 Caridad Freeway, Port Barabaraville, MS 66713

Phone: +3769542039359

Job: Sales Manager

Hobby: Baton twirling, Stand-up comedy, Leather crafting, Rugby, tabletop games, Jigsaw puzzles, Air sports

Introduction: My name is Frankie Dare, I am a funny, beautiful, proud, fair, pleasant, cheerful, enthusiastic person who loves writing and wants to share my knowledge and understanding with you.