SQL Datediff Advanced Usage

SQL DateDiff advanced usage?

DECLARE @BusHourStart DATETIME, @BusHourEnd DATETIME
SELECT @BusHourStart = '08:30:00', @BusHourEnd = '16:00:00'
DECLARE @BusMinutesStart INT, @BusMinutesEnd INT
SELECT @BusMinutesStart = DATEPART(minute,@BusHourStart)+DATEPART(hour,@BusHourStart)*60, 
@BusMinutesEnd = DATEPART(minute,@BusHourEnd)+DATEPART(hour,@BusHourEnd)*60 
DECLARE @Dates2 TABLE (ID INT, DateStart DATETIME, DateEnd DATETIME)
INSERT INTO @Dates2
SELECT 1, '15:00:00 04/29/2003', '11:00:00 04/30/2003' UNION
SELECT 2, '14:00:00 04/30/2003', '14:00:00 05/01/2003' UNION
SELECT 3, '14:00:00 05/02/2003', '14:00:00 05/06/2003' UNION
SELECT 4, '14:00:00 05/02/2003', '14:00:00 05/04/2003' UNION
SELECT 5, '07:00:00 05/02/2003', '14:00:00 05/02/2003' UNION
SELECT 6, '14:00:00 05/02/2003', '23:00:00 05/02/2003' UNION
SELECT 7, '07:00:00 05/02/2003', '08:00:00 05/02/2003' UNION
SELECT 8, '22:00:00 05/02/2003', '23:00:00 05/03/2003' UNION
SELECT 9, '08:00:00 05/03/2003', '23:00:00 05/04/2003' UNION
SELECT 10, '07:00:00 05/02/2003', '23:00:00 05/02/2003' 

-- SET DATEFIRST to U.S. English default value of 7.
SET DATEFIRST 7

SELECT ID, DateStart, DateEnd, CONVERT(VARCHAR, Minutes/60) +':'+ CONVERT(VARCHAR, Minutes % 60) AS ReactionTime
FROM ( 
    SELECT ID, DateStart, DateEnd, Overtime,
        CASE 
            WHEN DayDiff = 0 THEN 
                CASE 
                    WHEN (MinutesEnd - MinutesStart - Overtime) > 0 THEN (MinutesEnd - MinutesStart - Overtime) 
                    ELSE 0 
                    END
            WHEN DayDiff > 0  THEN 
                CASE 
                    WHEN (StartPart + EndPart - Overtime) > 0 THEN (StartPart + EndPart - Overtime) 
                    ELSE 0 
                    END + DayPart
            ELSE 0
        END AS Minutes 
    FROM(
        SELECT ID, DateStart, DateEnd, DayDiff, MinutesStart, MinutesEnd,
                CASE WHEN(@BusMinutesStart - MinutesStart) > 0 THEN (@BusMinutesStart - MinutesStart) ELSE 0 END +
                CASE WHEN(MinutesEnd - @BusMinutesEnd) > 0 THEN (MinutesEnd - @BusMinutesEnd) ELSE 0 END AS Overtime, 
                CASE WHEN(@BusMinutesEnd - MinutesStart) > 0 THEN (@BusMinutesEnd - MinutesStart) ELSE 0 END AS StartPart,
                CASE WHEN(MinutesEnd - @BusMinutesStart) > 0 THEN (MinutesEnd - @BusMinutesStart) ELSE 0 END AS EndPart,
                CASE WHEN DayDiff > 1 THEN (@BusMinutesEnd - @BusMinutesStart)*(DayDiff - 1) ELSE 0 END AS DayPart
        FROM (
                SELECT DATEDIFF(d,DateStart, DateEnd) AS DayDiff, ID, DateStart, DateEnd,  
                DATEPART(minute,DateStart)+DATEPART(hour,DateStart)*60 AS MinutesStart,
                DATEPART(minute,DateEnd)+DATEPART(hour,DateEnd)*60 AS MinutesEnd 
                FROM (
                        SELECT ID,
                                CASE 
                                        WHEN DATEPART(dw, DateStart) = 7 
                                        THEN DATEADD(SECOND, 1, DATEADD(DAY, DATEDIFF(DAY, 0, DateStart), 2))
                                        WHEN DATEPART(dw, DateStart) = 1 
                                        THEN DATEADD(SECOND, 1, DATEADD(DAY, DATEDIFF(DAY, 0, DateStart), 1))
                                ELSE DateStart END AS DateStart,
                                CASE 
                                        WHEN DATEPART(dw, DateEnd) = 7 
                                        THEN DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, DateEnd), 0))
                                        WHEN DATEPART(dw, DateEnd) = 1 
                                        THEN DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, DateEnd), -1))
                                ELSE DateEnd END AS DateEnd FROM @Dates2
                )Weekends
        )InMinutes
    )Overtime
)Calculation

Is SQL DATEDIFF(year, ..., ...) an Expensive Computation?

It depends on exactly what you are doing to be honest as to the extent of the performance hit.

For example, if you are using DATEDIFF (or indeed any other function) within a WHERE clause, then this will be a cause of poorer performance as it will prevent an index being used on that column.

e.g. basic example, finding all records in 2009

WHERE DATEDIFF(yyyy, DateColumn, '2009-01-01') = 0

would not make good use of an index on DateColumn. Whereas a better solution, providing optimal index usage would be:

WHERE DateColumn >= '2009-01-01' AND DateColumn < '2010-01-01'

I recently blogged about the difference this makes (with performance stats/execution plan comparisons), if you're interested.

That would be costlier than say returning DATEDIFF as a column in the resultset.

I would start by identifying the individual queries that are taking the most time. Check the execution plans to see where the problem lies and tune from there.

Edit:
Based on the example query you've given, here's an approach you could try out to remove the use of DATEDIFF within the WHERE clause. Basic example to find everyone who was 10 years old on a given date - I think the maths is right, but you get the idea anyway! Gave it a quick test, and seems fine. Should be easy enough to adapt to your scenario. If you want to find people between (e.g.) 15 and 17 years old on a given date, then that's also possible with this approach.

-- Assuming @Date2 is set to the date at which you want to calculate someone's age 
DECLARE @AgeAtDate INTEGER
SET @AgeAtDate = 10  

DECLARE @BornFrom DATETIME
DECLARE @BornUntil DATETIME
SELECT @BornFrom = DATEADD(yyyy, -(@AgeAtDate + 1), @Date2)
SELECT @BornUntil = DATEADD(yyyy, -@AgeAtDate , @Date2)

SELECT DOB
FROM YourTable
WHERE DOB > @BornFrom AND DOB <= @BornUntil

An important note to add, is for age caculates from DOB, this approach is more accurate. Your current implementation only takes the year of birth into account, not the actual day (e.g. someone born on 1st Dec 2009 would show as being 1 year old on 1st Jan 2010 when they are not 1 until 1st Dec 2010).

Hope this helps.

Using DATEDIFF to assign dynamic interval

You have to use dynamic sql for this. Once you start using dynamic sql you open yourself up to sql injection. Here is how you can do this and protect yourself against sql injection. The case expression may seem a little odd but if you have a value in your @interval that is not allowed the entire sql string will be NULL and no harm will come to your database. Also, please notice that you did not specify a length for @interval. For variables the default length is 1 so it would only be 'y' not 'year' as you expected it to be. Always specify the scale and precision of variables.

DECLARE @startdate datetime2 = '2007-05-05';  
DECLARE @enddate datetime2 = '2007-05-04';  
DECLARE @interval varchar(10) = 'year'

declare @SQL nvarchar(max)

set @SQL = 'select datediff(' + 
    case @interval when 'year' then 'year'
        when 'week' then 'week'
        when 'days' then 'day'
    end
    + ', @startdate, @enddate)'

exec sp_executesql @SQL, N'@startdate datetime2, @enddate datetime2', @startdate, @enddate

SQL Server Datediff for an entire table

Here is one option using datediff() in a lateral join and arithmetics:

select e.*, concat(d.diff / 60, 'hr, ', d.diff % 60, 'min') timediff
from dbo.employee e
cross apply (values (datediff(minute, e.timein, e.timeout))) d(diff)

Basically, the lateral join computes the difference between the two times in minutes. Then, the outer query builds the string that represents that interval as hours and minutes.

DATEDIFF function resulted in an overflow in SQL Server 2016

The problem here is the "starting date".

In select DATEDIFF(second, 0, t) 0 means 1900-01-01 that is too distant from 2017, so the time in seconds passed from 1900-01-01 to 2017-07-17 overflows simple integer, it's not just "60 seconds"

Why does the Datediff function show different values?

Try this to explain the logic:

select cast(0 as datetime)
select cast(1 as datetime)

An integer is interpreted as the number of Days since 1900-01-01 whereas a string value such as '1900' will be interpreted as a date format.

1900 Days from Jan 1st 1900 is 1905-03-16, which is five years from 1900 and 110 years from now (2015).

Issue with DateDiff function in SQL Server

You need to calculate the months and then you need to advance the start date by the number of months and calculate the days, like this:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

Query 1:

declare @f datetime, @t datetime
select @f='2015-01-25 00:00:00.000', @t='2015-04-28 00:00:00.000'
SELECT DATEDIFF(MONTH, @f, @t) as m, 
datediff(d, dateadd(month, DATEDIFF(MONTH, @f, @t), @f), @t) as d,
DATEDIFF(MONTH, @f, @t) + convert(float, datediff(d, dateadd(month, DATEDIFF(MONTH, @f, @t), @f), @t)) / 30.0 as md

Results:

| M | D |  MD |
|---|---|-----|
| 3 | 3 | 3.1 |

How does SQL Server calculate DATEDIFF function?

DATEDIFF

Returns the count (signed integer) of the specified datepart
boundaries crossed between the specified startdate and enddate.

The key word here is "boundaries".

The boundary of a week depends on the server settings (the week can start on Sunday or Monday, or any other day). See SET DATEFIRST

Boundary for months is the 1st day of the month, boundary for years is the 1st of January.

For example, both

DATEDIFF(Year, '2010-03-24', '2011-03-24')
DATEDIFF(Year, '2010-01-01', '2011-12-31')

would return 1, because there is only one 1st of January between both ranges of dates above.

---

Related Topics