SQL DATEDIFF() Function

The SQL DATEDIFF() is a function, and use to find the difference between two dates.

The SQL DATEDIFF() function returns a long value specifying the number of time intervals between two specified dates. We can get the difference in any type of datepart format like difference in days, month, year and etc.

It can be used in SELECT statement as well in where clause.

Datediff Calculate Age

Use MySQL's DATE_FORMAT() function to calculate age depending on birthdate. First, use the now() method to obtain the current date and time. You may then enter your birthdate in DATE_FORMAT().

Syntax:

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(now(),'yourDateofbirth')), '%Y')+0 AS anyVariableName;

Use the syntax above to compute age based on your date of birth. Replace yourDateofbirth with your own date of birth in the aforementioned syntax.

Example 1: It appears that using the DATEDIFF function is the quickest and simplest approach to determine the age of someone or something in years.

SELECT ImportantDateName,ImportantDate
GETDATE AS () [Today],
DATEDIFF(YY,ImportantDate,GETDATE()) AS [Age]
FROM tblImportantDate;

A deeper look reveals that the DATEDIFF function is not nearly as exact as we'd like (and certainly not as accurate as Keanu Reeves would like! ), despite the fact that at first glance it appears to effectively compute ages swiftly and effortlessly. In actuality, only the last record in the aforementioned list is computed accurately; the other three are reported to be one year older than they actually are.

The issue is that when the DATEDIFF function is instructed to look at years, it skips all other dates and, as a result, produces the calculation seen below:

SELECT  ImportantDateName,YEAR(ImportantDate) As [BirthYear]
YEAR (GETDATE()) AS [This Year]
YEAR (GETDATE())-YEAR(ImportantDate) As [Age]
FROM tblImportantDate;

Example 2: Calculation of the query is as follows:

SELECT DATE_FORMAT(FROM_DAYS(DATEIFF(now(),'2010-11-25')), '%Y')+0 AS Age;

Output:

Example 3: Let us now see this in an example.

Step 1: Firstly, create a table:

create table AgeCalculationFromDatetime
  (
  YourDateofBirth datetime
  );

Step 2: Inserting date of birth into table. The query is as follows:

insert into AgeCalculationFromDatetime values('1995-11-25');

Step 3: Displaying all records with the help of select statement. The query is as follows,

select *from AgeCalculationFromDatetime;

Output:

Step 4: The query to calculate age is as follows,

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(now(),YourDateofBirth)), '%Y')+0 
AS Age from AgeCalculationFromDatetime;

Output:

Example 4: I recently had to provide my age in years when filling out a form. I tried to calculate it in SQL Server using the DATEDIFF function as usual, but it returned me the incorrect result:

DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth',
     GETDATE() 'Current Date',
DATEDIFF(YEAR,@DOB,GETDATE()) 'Age in Years'

Incorrect Result:

The answer given by the aforementioned query is 32 years, which is 1 year too old. This error occurred because we were just considering the year and not the day and month. Therefore, if the birth date has not yet passed into the current year, we will receive 1 year more. In other words, if the birthday has already passed in the current year, the aforementioned query will return the correct age in years.

If the birth date hasn't yet passed into the current year, we have to subtract 1 year to resolve this problem:

DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth', GETDATE() 'Current Date',
DATEDIFF(YEAR,@DOB,GETDATE())
 -
 (CASE
 WHEN DATEADD(YY,DATEDIFF(YEAR,@DOB,GETDATE()),@DOB)
 >  GETDATE() THEN 1
 ELSE 0 END)
Age in Years'

Correct Result:

Example 5: Calculating Age

select ID,emp_name,emp_dateOfBirth from Employee;

We will compute the age in terms of a year, month, and days in two phases using the information from the Employee's birth date in the table above.

Step 1: Creating a function

CREATE FUNCTION fnEmpComputeAge(@EmpDOB DATETIME)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @AgeTempdate DATETIME, @AgeYears INT, @AgeMonths INT, @AgeDays INT
SELECT @AgeTempdate= @EmpDOB
SELECT @AgeYears=DATEDIFF(YEAR, @AgeTempdate,GETDATE())-CASE WHEN (MONTH(@EmpDOB)>MONTH(GETDATE()))
OR
(MONTH(@EmpDOB)=MONTH(GETDATE())AND DAY(@EmpDOB)>DAY(GETDATE()))THEN 1 ELSE 0 END
SELECT @AgeTempdate=DATEADD(YEAR, @AgeYears, @AgeTempdate)
SELECT @AgeMonths=DATEDIFF(MONTH, @AgeTempdate,GETDATE())-CASE WHEN DAY(@EmpDOB)>DAY(GETDATE())THEN 1 ELSE 0 END
SELECT @AgeTempdate=DATEADD(MONTH, @AgeMonths, @AgeTempdate)
SELECT @AgeDays=DATEDIFF(DAY, @AgeTempdate,GETDATE())
DECLARE @EmpAge NVARCHAR(50)
SET @EmpAge=Cast(@AgeYears AS NVARCHAR(4))+' AgeYears '+Cast(@AgeMonths AS NVARCHAR(2))
+' AgeMonths '+Cast(@AgeDays AS NVARCHAR(2))+' AgeDays Old'
RETURN @EmpAge
End

Explaination:

• In the previous example, we constructed a SQL Function that takes @EmpDOBas as a parameter and returns NVARCHAR(50) to determine the age of the employee from the DOB. When we use this function, we will observe this in action. We have developed this function step-by-step.
• Then, @AgeTempdate DATETIME, @AgeYearsINT, @AgeMonthsINT, and @AgeDaysINT variables were declared. The @AgeTempdate has first been set to the @EmpDOB. The crucial part of the next sentence is when we use the DATEDIFF function to determine the year difference between the dob and the current date, which is determined using the GETDATE function. Then, depending on whether the dob month is greater than the current month or whether it is the same as the current month and the dob date is greater than the current date, we subtract 1 or 0 or, alternatively, add 1 or add 0.
• The DATEADD function is then used to add the calculated years to the @AgeTempdate.
• In a similar manner, the month was computed, @AgeTempdate was added, and the days were then computed. The final step was to declare @EmpAge and set the final output concatenation as its value. We used the Cast function to change the calculation result from int to nvarchar because it was int originally.

Step 2: Using the function in the query

select ID,emp_name,emp_dateOfBirth,
dbo.fnEmpComputeAge(emp_dateOfBirth) as EmpAge from Employee

As we can see, in order to calculate EmpAge, we used the dbo.fnEmpComputeAge function and gave emp_dateOfBirth as a parameter. The outcome is shown above.

This function returns the number of times the specified datepart boundaries were crossed between the supplied startdate and enddate (as a signed integer value).

For example, SELECT DATEDIFF(day, '2036-03-01', '2036-02-28'); returns -2, hinting that 2036 must be a leap year. This case means that if we start at startdate '2036-03-01', and then count -2 days, we reach the enddate of '2036-02-28'.

Example 1: Specifying columns for startdate and enddate:

This example calculates the number of day boundaries crossed between dates in two columns in a table.

CREATE TABLE dbo.Duration  
(startDate datetime2, endDate datetime2);  
    
INSERT INTO dbo.Duration(startDate, endDate)  
    VALUES ('2007-05-06 12:10:09', '2007-05-07 12:10:09');  
    
SELECT DATEDIFF(day, startDate, endDate) AS 'Duration'  
    FROM dbo.Duration;  

Example 2: Using DATEDIFF() function and getting the negative difference between the two values of dates, in day.

SELECT DATEDIFF(day, '2021/2/1', '2010/12/12');

Output:

while figuring out how much time has passed since a certain date or when comparing a past date to the today.

Syntax:

Months:

select datediff (mm, fromDATE, toDATE) 

Years:

select datediff (yy, fromDATE, toDATE) 

Minutes:

select datediff (mi, fromDATE, toDATE) 

Example 1: For example – how old is an invoice? How many days overdue is an invoice? Below is some SQL code that will help.

First, if you need to dynamically get the current date (and time) in SQL:

select getdate();

To calculate the difference between two dates and get the result in days:

select datediff (d, fromDATE, toDATE) 

For example, this code:

select datediff (d, '8/24/08', '10/27/08')

Result:

Remember to put the earlier date first for a successful outcome. You could also want to know the difference in months, years, or even in milliseconds rather than days at times.

Example 2: Rounding down, all of the aforementioned will produce integer results. For differences in days, that is usually sufficient, but for years, it is usually more helpful to see the findings with decimal places. In this instance, I calculate the results in days and multiply by 365 (or 365.25 if I believe I am using a sufficiently long time period to take into account leap years):

select cast(datediff(d, fromDATE, toDATE) AS DECIMAL (12,1))/365

Here is an example that also demonstrates the leap year difference. Divide by 365 today:

select cast(datediff(d, '8/19/1971', getdate()) AS DECIMAL (8,1))/365

Result:

Example 3: divide by 365.25 today:

select cast(datediff(d, '8/19/1971', getdate()) AS DECIMAL (8,1))/365.25

Result:

If you're going to be performing computations with that many years, there's not a large difference, but it could be a little more accurate.

Example 1: The difference between the hours of the two previous timestamps is calculated using the interval hour.

SELECT DATEDIFF(hour, '2020/5/20 08:00', '2020/5/20 15:00') AS Output;

Output:

Example 2: Using the DATEDIFF() method, one can calculate the hourly difference between two dates that both contain time.

SELECT DATEDIFF(hour, '2019/2/1 09:55', '2020/12/12 07:45');

Result:

Example 3: In addition, you can retrieve the intervals of time between the dates. An illustration of returning the amount of hours between the date and time values is as follows:

DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000';
DECLARE @date2 datetime2 = DATEADD(hour, 1, @date1);
SELECT 
DATEDIFF( hour, @date1, @date2 ) AS Hours,

Result:

The DATEDIFF() method is used to determine the difference between two dates in years, months, weeks, etc.

Syntax:

DATEDIFF( date_part , start_date , end_date)

The DATEDIFF() function accepts three arguments: date_part, start_date, and end_date.

date_part is the part of date e.g., a year, a quarter, a month, a week that you want to compare between the start_date and end_date.

start_date and end_date are the dates to be compared. They must be resolved to values of type DATE, DATETIME, DATETIMEOFFSET, DATETIME2, SMALLATETIME, or TIME.

Example 1: Here we used to datediff months:

DECLARE 
@start VARCHAR(10) = (
  SELECT order_date FROM demo_orders
  WHERE item_name = 'Maserati'),
@end VARCHAR(10) = (
  SELECT order_date FROM demo_orders
  WHERE item_name = 'Ferrari')

• @start variable holds the start date(i.e date of Maserati being purchased).
• @end variable holds the end date (i.e date of Ferrari being purchased).

SELECT DATEDIFF(month, @start, @end) AS number_of_months;

Instead of month, we might use year or day, which would provide the appropriate number of years and days between the two dates.

Example 2: To determine the difference between two dates in Sql Server's Months, use the DATEDIFF() method as shown below. The first parameter value for the DATEDIFF() function can be month, mm, or m, and they will all provide the same output. The example below demonstrates how to determine the difference between two dates in months.

DECLARE @FromDate DATETIME = '2014-12-31', 
   @ToDate DATETIME = '2015-01-01'
SELECT @FromDate 'From Date', @ToDate 'To Date',
   (DATEDIFF(MONTH, @FromDate, @ToDate)
   -
   (CASE WHEN DATEADD(MONTH, 
   DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate)
   > @ToDate
   THEN 1 ELSE 0 END)) 'Date difference in Months'

Result:

You wish to find the difference between two datetime-type columns that you have.

Example 1: Id, departure, and arrival are the three columns in the journey table. You want to figure out how much the arrival and departure differ from one another.

The travel table looks like this:

id	departure		    arrival
1	2018-03-25 12:00:00	2018-04-05 07:30:00
2	2019-09-12 15:50:00	2019-10-23 10:30:30
3	2018-07-14 16:15:00	2018-07-14 20:40:30
4	2018-01-05 08:35:00	2019-01-08 14:00:00

Solution (difference in seconds):

SELECT id,
  departure,
  arrival,
  DATEDIFF(second, departure, arrival) AS difference
FROM travel;

Output:

id	departure	        arrival	            difference
1	2018-03-25 12:00:00	2018-04-05 07:30:00	934200
2	2019-09-12 15:50:00	2019-10-23 10:30:30	3523230
3	2018-07-14 16:15:00	2018-07-14 20:40:30	15930
4	2018-01-05 08:35:00	2019-01-08 14:00:00	31814700

Discussion: Use the DATEDIFF(datepart, startdate, enddate) function in T-SQL to determine the difference between the arrival and departure. A microsecond, second, minute, hour, day, week, month, quarter, or year can be used as the datepart argument. In this case, you want to know the difference in seconds, so pick second. Choose hour to obtain the difference in hours, month to obtain the difference in months, etc. The starting and ending datetime columns are represented by the startdate and enddate parameters, respectively (here, departure and arrival, respectively).

Example 2: Calculating the amount of time (in seconds, milliseconds, or even nanoseconds) between two events is one of the most frequent scenarios. With demo data in the startdate and enddate parameters, the code might resemble this:

DECLARE @dt1 AS DATETIME2(7) = CAST('2018-11-14 09:05:12.2390114' AS DATETIME2(7));
DECLARE @dt2 AS DATETIME2(7) = CAST('2018-11-14 09:02:03.8440911' AS DATETIME2(7));
SELECT DATEDIFF(SECOND, @dt1, @dt2);

The outcome in this fictitious example is an integer with the value -189, or 189 seconds. Be aware that the outcome would be a positive number if the input values were flipped. In most cases, we would use the SQL system function to convert this to an absolute value in order to avoid negative numbers.

Similar to ADDTME(), MySQL's TIMEDIFF() function determines the difference between two times. However, there isn't a specific SQL Time function in Microsoft SQL Server that may be used to subtract one time value from another. To extract the difference between two time values stored in the database, use the DATEDIFF() function with the DATEPART parameters. the many DATEDIFF() Examples to comprehend how to use it to calculate the difference between two time values and analyze the difference between two date values.

It should be mentioned that the SQL TIME function can be used to determine the difference in time between two DateTime values in terms of hours, minutes, seconds, microseconds, years, months, weeks, and days, among other units. In our prior post, "SQL Date Functions," we previously explained how to use this function to calculate dates. Here, comparing time data and calculating the amount of hours, minutes, seconds, and microseconds, among other things, will be our goal.

Note: Another iteration of this SQL date and time method is called DATETIME_BIG(), and it is used to compute the difference between two DateTime values with bigint results in terms of hours, minutes, and seconds, among other time units.

Syntax:

DATEDIFF(interval, datetime1, datetime2)

By subtracting datetime1 from datetime2, this function returns the difference based on the interval of the first parameter. Once more, interval refers to the area of the DateTime where you wish to compute the difference. The following is a list of date_part argument lists that can be used as "intervals":

• hour, hh = hour
• minute, mi, n = Minute
• second, ss, s = Second
• millisecond, ms = Millisecond
• microsecond, mcs = Microsecond
• nanosecond, ns = Nanosecond

Example 1: Let's say you want to figure out how many hours, minutes, and seconds there are between December 31, 2019, and the system's CURRENT Date. It was sent to remind you that on December 31, 2019, Wuhan City, China, had cases of pneumonia with an unknown cause. Chinese authorities discovered the root of the problem on January 7, 2020, and it was given the temporary name "2019-nCoV."

Here is the SELECT Statement:

DECLARE @StartDate datetime2 = GETDATE()
DECLARE @EndDate datetime2='2019/12/31 12:45:31.348902' 
SELECT DATEDIFF(hour, @EndDate, @StartDate) AS HoursDiff,
DATEDIFF(minute, @EndDate, @StartDate) AS MinutesDiff,
DATEDIFF(second, @EndDate, @StartDate) AS SecondsDiff,
DATEDIFF_BIG(ms, @EndDate, @StartDate) AS MicroSecDiff;

As you can see, the syntax is similar to that of the SQL function DATEADD(). The value returned can overflow, so DATEDIFF BIG is utilized to determine the difference in milliseconds. The two dates are declared using the DECLARE statement.

SQL SERVER Time Functions - Subtracting time using DATEDIFF():

Hours between the SELECT Statement's @Startdate and @Enddate are mentioned. In the same way, MinutesDiff, SecondsDiff, and MicroSecDiff display the difference in minutes, seconds, and microseconds, correspondingly.

Example 2:

For these types, a time of day and an optional time zone constitute valid input. When entering time without a time zone, any time zone information is silently disregarded. Other than when using a time zone name that incorporates a daylight-savings rule, such as America/New_York, you can also give a date, but it will be ignored. To know whether standard time or daylight saving time applies in this situation, the date must be specified. The time with the time zone value includes the proper time zone offset.

Time Input

Time Zone Input - specify time zones.

Time Stamps

The concatenation of a date and time, followed by an optional time zone, followed by an optional AD or BC, constitutes valid input for time stamp types. (Alternatively, the time zone can come before AD/BC, but this is not the recommended ordering.)

Thus:

1999-01-08 04:05:06 and: 1999-01-08 04:05:06 -8:00

Are valid values, which follow the ISO 8601 standard. In addition, the common format:

January 8 04:05:06 1999 PST is supported.

By including a "+" or "-" sign and a time zone offset after the time, the SQL standard distinguishes between timestamps with and without time zones. Consequently, by the standard,

A timestamp without a time zone is "2004-10-19 10:23:54," while one with a time zone is "2004-10-19 10:23:54+02."

A timestamp that includes the time zone. PostgreSQL will treat both of the aforementioned as timestamps without a time zone since it never looks at the content of a literal string before establishing its type. Give a literal the proper explicit type to make sure it is recognized as a timestamp with time zone:

TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'

PostgreSQL will silently disregard any time zone information in a literal that has been judged to be a timestamp without a time zone. In other words, the output value does not account for time zone and instead derives from the date and time fields in the input data.

Internally stored values for timestamps with time zones are always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An explicit time zone-specified input value is translated to UTC using the correct offset for that time zone. If the input string doesn't specify a time zone, it is presumed to be in the one specified by the system's TimeZone parameter and converted to UTC using the timezone offset.

A timestamp with a time zone value is always translated from UTC to the current time zone zone and presented as local time in that zone when it is output. Change your time zone or use the AT TIME ZONE construct to view the time in a different time zone.

The standard assumption in conversions between timestamps with and without time zones is that the timestamp without time zone value should be used or provided as timezone local time. Using AT TIME ZONE, an alternative time zone can be chosen for the conversion.

Utilizing the DATEDIFF() function in a SQL query, determine the number of weeks that exist between the two dates.

similar function to calculate with dates:

DateDiff(interval, date1, date2 [,firstdayofweek[, firstweekofyear]])

Unfortunately, this last argument isn’t available in Sql Server.

Example 1: Here’s another example where I declare two variables and assign two different dates to them (I use DATEADD() to add 1 year to the first date). I then use DATEDIFF() to return various dateparts for that date:

DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000';
DECLARE @date2 datetime2 = DATEADD(year, 1, @date1);
SELECT DATEDIFF( week, @date1, @date2 ) AS Weeks;

Output:

Example 2: This method has a problem since Sql Server believes the week begins on Sunday. This may be true in some circumstances, but with the project I'm working on right now, the week should begin on Monday. You may learn more about how to resolve the datediff and weeks problem in this article.

SELECT DATEDIFF(wk,'2012-01-07', '2012-01-08')

7th of January is saturday, 8th of January is sunday. The answer from SQL Server is 1. That’s true because the new week starts on sunday.

Example 3: SET LANGUAGE Dutch

After running this SET LANGUAGE command, I run the DateDiff command again:

SELECT DATEDIFF(wk,CAST('2012-01-07' AS DATETIME), CAST('2012-01-08' AS DATETIME) )

The date notation is YYYYDDMM so SQL server thinks that I mean July – August and then the answer is correct.

Example 4: reverse the month and day:

SELECT DATEDIFF(wk,CAST('2012-07-01' AS DATETIME), CAST('2012-08-01' AS DATETIME) )

This function always does the computation assuming Sunday as the first day of the week, regardless of the language option.

Example 5: cast an integer to datetime, the language setting will be ignored:

SELECT CAST('2012-01-09'  AS DATETIME) AS DATEVALUE
SELECT CAST(201201|09'    As DATETIME) AS DATEVALUE
SELECT @@DATEFIRST;

Example 6:

SET LANGUAGE us_english
SELECT @@DATEFIRST;

The first day of the week is returned as a number by this method. It is 1 in the Netherlands. That indicates Monday. In the US, 7 denotes Sunday. Knowing this allows me to write a tidy statement to perform the calculation.

SET LANGUAGE Dutch;
SELECT DATEDIFF( week,
DATEADD(DAY, -@@DATEFIRST, '20091227'),
DATEADD(DAY, -@@DATEFIRST, '20091228') );

Example 7: Easy way: we know that @@DATEFIRST always returns 1 in my situation. So this also works:

SELECT DATEDIFF( week,
DATEADD(DAY, -1, '20120108'),
DATEADD(DAY, -1, '20120109') ) AS WeekDifference;

• Friday to Saturday becomes Thursday to Friday: Difference = 0 weeks (before: 0 weeks)
• Saturday to Sunday becomes Friday to Saturday: Difference = 0 weeks (before: 1 week)
• Sunday to Monday becomes Saturday to Sunday: Difference = 1 weeks (before: 0 week)

Example 8: Here, we'll look at how to use a SQL query and the DATEDIFF() function to determine how many weeks there are between the two specified dates.

We'll make a demo orders table in a database called basic for demonstration purposes.

Step 1: Creating the Database:

Use the below SQL statement to create a database called basic:

CREATE DATABASE basic;

Using the Database:

Use the below SQL statement to switch the database context to basic:

USE basic;

Step 2: Create Table :

We have the following demo table in our basic database.

CREATE TABLE demo_orders(
ORDER_ID INT IDENTITY(1,1) PRIMARY KEY,
ITEM_NAME VARCHAR(30) NOT NULL,
ORDER_DATE DATE NOT NULL
);

You can use the below statement to query the description of the created table:

EXEC SP_COLUMNS demo_orders;

Step 3: Adding data to the table:

Use the below statement to add data to the demo_orders table:

INSERT INTO demo_orders --no need to mention columns explicitly as we are inserting
                        --into all columns and ID gets
                          --automatically incremented.
VALUES
('Maserati', '2007-10-03'),
('BMW', '2010-07-23'),
('Mercedes Benz', '2012-11-12'),
('Ferrari', '2016-05-09'),
('Lamborghini', '2020-10-20');

To verify the contents of the table use the below statement:

SELECT * FROM demo_orders;

Step 4: Now let's use the DATEDIFF() method to determine how many weeks there were between the dates of the order for the 'Maserati' and the 'Ferrari' in the database.

DECLARE 
@start VARCHAR(10) = (
  SELECT order_date FROM demo_orders
  WHERE item_name = 'Maserati'),
@end VARCHAR(10) = (
  SELECT order_date FROM demo_orders
  WHERE item_name = 'Ferrari')  

--@start variable holds the start date(i.e date of Maserati being purchased).
--@end variable holds the end date (i.e date of Ferrari being purchased).

SELECT DATEDIFF(ww, @start, @end) AS number_of_weeks;

Output:

Example 9: The week interval calculates the number of weeks present between the two given timestamps.

SELECT DATEDIFF(week, '2020/5/20', '2020/6/20') AS Output;

Output:

The calculation of working hours based on a given date range.

Example 1: sql datediff working hours

Step 1: Calculate total working days

In this phase, we compute the total number of working days using a method similar to that used in the earlier parts. The output is not being increased by one as is shown, which is the only difference.

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 following step consists of finding the difference in seconds between the two dates and turning that difference into hours by dividing by 3600.0 as demonstrated.

SET @TotalTimeDiff =
(
 SELECT DATEDIFF(SECOND,
    (
     SELECT CONVERT(TIME, @DateFrom)
    ),
    (
    SELECT CONVERT(TIME, @DateTo)
    )) / 3600.0
);

Step 3: The final step entails multiplying the result of Step 1 by 24 (the total number of hours in a day), then adding that result to Step 2 result illustrated below.

SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff;

Step 4: Lastly, the entire script that can be used to develop a user-defined function for calculating working hours is displayed in Script 16 along with an example of how it may be applied.

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

Example 2: Calculating the number of working days or hours between two timestamps is a common problem in business. We define working hours as the period of time spent on working days—excluding holidays—between a start time (let's say, 9am) and an end time (let's say, 6pm). For instance, a project that lasted six working hours from Monday at 5 p.m. through Tuesday at 2 p.m (5pm-6pm on Monday and 9am-2pm on Tuesday).

Here is a clear explanation of how to calculate the number of elapsed weekdays. This method, however, does not take into account holidays and does not readily apply to figuring out working hours.

The method for quickly determining the working hours between two timestamps while accounting for holidays is described in this article.

We must first create a reference table with all the time periods that constitute "working hours." One row every day in this table lists the start and end times of the working day as well as whether it is a working day or a holiday.

This strategy is advantageous for various reasons:

• Ease of Use - Utilizing this, we can create a brief query that calculates working hours.
• Auditability – If your organization observes unusual holidays, it is simple to determine which days are being counted as working days.
• Flexibility – this strategy enables us to set unconventional working hours on specific days (for example, a half-day). We can even use many intervals for a more captivating example.

For example, if your working hours include a lunch break from 12pm to 1pm, you can have two rows for the same day with two intervals, 9am to 12pm and 1pm to 6pm.

Let's say that we have an input table of "time worked" intervals and that we want to determine the number of working hours that have passed for each work interval now that we have our reference table. To arrive at this conclusion, we'll use the following method:

• Filter the reference table for each work interval to only show intervals that cross it (the reference intervals).
• Determine where each reference interval and the work interval intersect.
• Determine each intersection's timing difference.
• Add up the time differences between each reference period.

Let's go over step 2 in further detail. The intersection of two intervals can be determined using a relatively straightforward approach. Our intervals are (a1, a2) and, let's assume (b1, b2). The intersection of the two intervals is obtained by (max(a1, b1), min(a2, b2), as seen in the following image.

We are now prepared to write some code after completing those procedures.

Here is an illustration of how the aforementioned algorithm might be used in SQL.

select t.id
  , sum(datediff('second', 
  -- calculate the max of the two start time
 (case when t.start <= 
  w.working_day_start_timestamp
  then w.working_day_start_timestamp
  else t.start
  end),
  
  -- calculate the min of the two end times
  (case when t.end >= 
  w.working_day_end_timestamp
  then w.working_day_end_timestamp
  else t.end 
  end)
  )) / 3600 -- convert to hourly
  as working_hour_diff
from 
  working_days_times w
  cross join time_intervals t
where -- select all intersecting intervals
  (
   t.start <= w.working_day_end_timestamp
   and
   t.end >= w.working_day_start_timestamp
  )
and -- select only working days
  w.is_working_day
group by t.id;

Example 1:

Approach 1:

To find the difference between two dates in Years in SQL Server, we may use the DATEDIFF() function as shown below. Year, yyyy, or yy are all acceptable values for DATEDIFF(first )'s parameter; all will provide the same outcome.

The example below demonstrates how to determine the difference between two dates in years.

DECLARE @FromDate DATETIME = '2014-12-31', 
        @ToDate DATETIME = '2015-01-01'
SELECT @FromDate 'From Date', @ToDate 'To Date',
 DATEDIFF(YEAR, @FromDate, @ToDate)
   -
 (CASE
   WHEN DATEADD(YEAR, 
   DATEDIFF(YEAR, @FromDate,@ToDate), @FromDate)
   > @ToDate THEN 1 
   ELSE 0 END) 'Date difference in Years'

Result:

You might be wondering why Apporach 1 employs such convoluted reasoning when it could have used a single DATEDIFF() function to determine the difference between two dates in years. The use of such intricate logic is necessary because the DATEDIFF() method returns the number of boundaries that the given datepart has crossed between the given fromdate and enddate.

Approach 2:

In essence, it determines the difference between two dates by discarding all dateparts from both dates that are smaller than the chosen datepart. Let's use an example to better grasp this.

DECLARE @FromDate DATETIME = '2014-12-31', 
        @ToDate DATETIME = '2015-01-01'
SELECT @FromDate 'From Date', @ToDate 'To Date',
 DATEDIFF(YEAR, @FromDate, @ToDate) 'Date difference in Years'

Output:

It is evident from the above result that even if @ToDate is the day after @FromDate, the difference returned is still 1 year. So adopt the best strategy based on the situation. If age calculation is required, Approach1 should be used.

Example 2: Using the DATEDIFF SQL function, we will obtain the years between two dates in this example. The end date is again the system's current time, whereas the start date is a constant value (assume this is 2018). See the inquiry and outcome:

SELECT DATEDIFF(year, '2010-03-13', GETDATE())
AS "Years Difference";

Output:

Example 3: The difference between the years passed in the function parameter results from the fact that in this example we gave "year" as the interval.

SELECT DATEDIFF(year, '2020/5/20', '2025/10/15') AS Output;

Output:

To calculate the difference between a due date and today and to display the result as a + rather than a -, create the expression for DateDiff. I keep getting a negative number. Otherwise, the number is accurate.

There are two ways to return a positive value if you receive a negative response. In your DateDiff function, you can first change the values such that the smaller date appears first. Alternately, you may use the Abs function to return the result's absolute value.

Example 1: For example, if the following DateDiff function is return a negative value:

Expr1:

DateDiff('d',[Due_Date],Date())

You could fix the DateDiff function with either of these solutions:

2: Switch the order of the dates

Expr1:

DateDiff('d',Date(),[Due_Date])

DateDiff will yield a positive number if the dates are reversed so that the smaller date appears first.

3: Use the Abs function

Expr2:

Abs(DateDiff('d',[Due_Date],Date()))

By using the Abs function on the result of the DateDiff function, you will always get a positive value.

Example 2: This Date and Time Function Example will check the employee information (such as: Which date we hired him, how long he associated with this company, etc.). We're using the DATEDIFF function in this SQL example to determine the current date.

SELECT [FirstName] + ' '+ [LastName] AS [Full Name]
      ,[Occupation]
      ,[YearlyIncome]
      ,[HireDate]
      ,DATEDIFF (year, [HireDate], GETDATE()) AS [YEARS]
      ,DATEDIFF (quarter, [HireDate], GETDATE()) AS [QUARTERS]
      ,DATEDIFF (month, [HireDate], GETDATE()) AS [MONTHS]
      ,DATEDIFF (day, [HireDate], GETDATE()) AS [DAYS]
FROM [Employee]

As you can see, we utilized the DATEDIFF function to compare the Employee Hire date to Today (GETDTE()) and display the difference.

Example 3: Calculating the difference between two dates or the difference between a date and today is a common task while creating a report. How old is a bill, for instance? How long is an invoice past due? Some helpful SQL code is provided below.

First, if you need to dynamically get the current date (and time) in SQL:

select getdate()

To calculate the difference between two dates and get the result in days:

select datediff (d, fromDATE, toDATE) 

For example, this code:

select datediff (d, '8/24/08', '10/27/08')

Output:

Remember to put the earlier date first for a successful outcome. You could also want to know the difference in months, years, or even in milliseconds rather than days at times.

Here is the syntax:

Months:

select datediff (mm, fromDATE, toDATE) 

Years:

select datediff (yy, fromDATE, toDATE) 

Minutes:

select datediff (mi, fromDATE, toDATE) 

Rounding down, all of the aforementioned will produce integer results. For differences in days, that is usually sufficient, but for years, it is usually more helpful to see the findings with decimal places. In this instance, I calculate the results in days and multiply by 365 (or 365.25 if I believe I am using a sufficiently long time period to take into account leap years):

select cast(datediff(d, fromDATE, toDATE) AS DECIMAL (12,1))/365

Example 4: Here is an example that also demonstrates the leap year difference. Divide by 365 today:

select cast(datediff(d, '8/19/1971', getdate()) AS DECIMAL (8,1))/365

Result:

And divide by 365.25 today:

select cast(datediff(d, '8/19/1971', getdate()) AS DECIMAL (8,1))/365.25

Result:

Not a huge difference, but a little more precise if you’re going to be doing calculations with that many years.

Weekends not included (using DATEDIFF function to identify employees first 3 days as training).

There were a few results when I searched for Exclude Weekends and Exclude Weekends using the DateDiff Function, but none of them seemed to work for what we're attempting to do, so I'm looking to see if we can get any assistance on this!

We're attempting to add a transform that would create a new column by computing: to the dataflow itself as part of the output dataset.

1. An employee's first three CONSECUTIVE days of employment, which we would often classify as TRAINING, i.e. Employee Start Date - Date when the difference is less than two.
2. do not include weekends because training is not conducted on weekends; therefore, a worker who began work in the middle of the week, say on a Thursday, would have received training on Thursday, Friday, and the following Monday.
3. The objective is to mark those days as YES in a new column (EMPLOYEE TRAINING), and then utilize that column as a quick filter to remove training days from the billing reconciliation with outside partners who supply staffing solutions for our call center.

Example 1: We can use the dataflow to identify JUST the first three days of employment (it seems to work for some, doesn't for others, is hit or miss, but certainly doesn't work at all for those who receive training before and after a weekend) as follows:

CASE WHEN DATEDIFF(`Date`,`Start Date`) <=2 
AND 
`User Company` = 'XXX' THEN 'Yes' ELSE 'No' END as `Employee Training`

SELECT DATEDIFF(dd, ActualStartDate, ActualCompletionDate) AS TimeTaken
FROM TableName

Now I want to exclude weekends and only include Mon-Fri as days counted.

We can determine the number of weeks between two dates by using DATEDIFF(WK,...). Instead of basing its evaluation on the number of days, SQL Server considers this to be a difference between weeks. This is ideal since we can use it to calculate the number of weekends that separated the dates.

As a result, we can multiply that value by two to determine how many weekends there were, and then we can remove that amount from the DATEDIFF(dd,...) to get how many weekdays there were.

However, when the start or end date comes on a Sunday, this doesn't always work correctly. To deal with those situations, I thus inserted some case logic at the conclusion of the calculation.

Example 2: You might also want to think about whether the DATEDIFF needs to be completely inclusive. For instance, is there a one-day or two-day difference between 9/11 and 9/10? If the latter, you should increase the final product by 1.

declare @d1 datetime, @d2 datetime
select @d1 = '9/9/2011',  @d2 = '9/18/2011'

select datediff(dd, @d1, @d2) - (datediff(wk, @d1, @d2) * 2) -
    case when datepart(dw, @d1) = 1 then 1 else 0 end +
    case when datepart(dw, @d2) = 1 then 1 else 0 end

Example 3: Recently, I was asked how to find the number of working days in SQL Server between two dates. The request further specified that the start date should only be counted as one of the days if the hour is earlier than 3:00 PM, adding another layer of complexity. I started with the fundamentals of working with dates in SQL and built on them to arrive at the solution rather than going right to the conclusion. It's a question that comes up frequently, so it would make for a fantastic blog topic. I will now present to you:

Working with dates in SQL?

DateTime objects in SQL can be broken down into individual parts using the 'DATEPART' function. The 'DATEPART' function takes 2 parameters: the interval and the date. The interval is the part of the date that you want to return, such as “year”, “month”, “day”, “hour”, etc. And the date is the value being evaluated:

select DATEPART(MONTH, '1/20/2018') -> returns "1"
select DATEPART(DAY, '3/21/2018') -> returns "21"
select DATEPART(YEAR, '4/23/2018') -> returns "2018"

Example 4: "WEEKDAY," a different acceptable interval portion, produces a number between 1 and 7. Counting from 1 to 7, where 1 is Sunday. Using that, we can make a straightforward check to determine whether a particular day is a weekend day. That would seem as follows:

select
CASE
WHEN DATEPART(WEEKDAY, '6/25/2018') IN (1, 7)
THEN 'Weekend'
ELSE 'Weekday'
END

This is good to know and comes in handy quite often, but it doesn't get us to where we want to be yet.

Example 4: 'DATEDIFF' is a function that determines the difference between two dates and returns the result in the units of measurement we specify. The 'DATEPART' function that we previously covered has the same input parameters as this function. With this, we may determine how many minutes, hours, days, weeks, etc. separate two dates:

select DATEDIFF(DAY, '1/1/2018', '2/1/2018') -> returns "31"
select DATEDIFF(WEEK, '1/1/2018', '2/1/2018') -> returns "4"

Example 5: Now that we are aware of the total number of days and weeks that separate two dates, we may compare them. Assuming there are two weekends per week, the number of workdays would be the total number of days less two. Such a statement would appear in a SQL query like this:

select DATEDIFF(DAY, '1/1/2018', '2/3/2018'),  
DATEDIFF(WEEK, '1/1/2018', '2/3/2018') * 2 -> returns "25"

Example 6: Since our finish date (2/3/2018) falls on a weekend, what we have so far isn't quite accurate, but we are coming closer to the final solution. If either the start date or the end date falls on a weekend day, we will need to deduct one from each to accommodate for that. This is how the SQL for that looks:

select DATEDIFF(DAY, '1/1/2018', '2/3/2018')
- DATEDIFF(WEEK, '1/1/2018', '2/3/2018') * 2
- CASE
WHEN DATEPART(WEEKDAY, '1/1/2018') in (1, 7) THEN 1 ELSE 0
  END
- CASE
WHEN DATEPART(WEEKDAY, '2/3/2018') in (1, 7) THEN 1 ELSE 0
  END

And so, excluding the start date, there are 24 weeks between January 1, 2018, and February 3, 2018. If the start date is earlier than 3:00 PM, the only step that needs to be done is to add 1.

Example 7: For the sake of convenience, I have used dates as examples thus far, although DateTime values might also be used instead. The final "CASE" phrase is crucial to this step:

select
DATEDIFF(DAY, '1/1/2018 08:00', '2/3/2018 08:00')
- DATEDIFF(WEEK, '1/1/2018 08:00', '2/3/2018 08:00') * 2
- CASE
WHEN DATEPART(WEEKDAY, '1/1/2018') in (1, 7) THEN 1 ELSE 0
  END
- CASE
WHEN DATEPART(WEEKDAY, '2/3/2018') in (1, 7) THEN 1 ELSE 0
  END
+ CASE
WHEN DATEPART(HOUR, '1/1/2018 08:00') < 15 THEN 1
ELSE 0
  END

And now we have 25 because the "HOUR" part of the start date is before 3:00 PM (15).

Now, I have used all of this useful information numerous times over the years. In most cases, this is enough for all of your daily computation needs. However, keep in mind that "1/1/2018" was a holiday, so if you need to account for holidays, our computation is wrong by one day. No built-in feature of SQL Server can inform you whether a specific day is a holiday. It takes a table of holidays to accomplish that. This is not at all unusual; many businesses will have a table with a record for each day and a bit value indicating whether the day is a workday or not.

Example 8: For the purposes of this example, let's assume that we have a table called "WORKCALENDAR" with one record every day, each of which has a date and the field "WORKTIME" in it. There will be a value of 0 for working days and 1 for non-working days in that field (weekends and holidays). And with that, we can determine the number of workdays between two dates without using the DATEPART or DATEDIFF methods. By counting the number of records between two days where "WORKTIME = 0," we can arrive to the same conclusion:

select count(*)
from WORKCALENDAR
where DATEVALUE >= '1/1/2018'
and DATEVALUE <= '2/3/2018'
and WORKTIME = 0

Example 9: We’re almost done! Remember that we still need to add 1 if the "HOUR" part of the start date is less than 15. So the final query becomes:

select count(*)
+ CASE
WHEN DATEPART(HOUR, '1/1/2018 08:00') < 15 THEN 1
ELSE 0
  END
from WORKCALENDAR
where DATEVALUE >= '1/1/2018 08:00'
and DATEVALUE <= '2/3/2018 08:00'
and WORKTIME = 0

Typically, the value we are computing is just a small portion of a larger query that may also include a huge number of other parameters. The example queries would then be added to the larger query as a nested query in those circumstances (also known as a subquery).

Example 10: This last illustration brings everything together in a query that returns the order numbers and the number of working days between the order date and the present:

select ORDERNUMBER, po.ORDERDATETIME,
(
select COUNT(*)
+ CASE
WHEN DATEPART(HOUR, po.ORDERDATETIME) < 15 THEN 1 ELSE 0
  END
FROM WORKCALENDAR
WHERE DATEVALUE >= po.ORDERDATETIME
and DATEVALUE <= GETDATE()
and WORKTIMECONTROL = 0
) as DaysElapsed
from PURCHORDERS po
where po.ACCOUNT = 'ACCT555'

That's all, then! We've discussed a query that determines the number of working days between two dates using two distinct approaches, one of which can even take special holiday schedules into consideration.