Home › SQL Server › SQL Server DATE calculations

SQL Server Date Difference Calculation


Find the number of YEARS between two dates

DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '1997-01-01',
    @d2 = '2009-12-31'
    
SELECT
    DATEDIFF(year, @d1, @d2) AS YearDiff
/*
YearDiff
-----------
12
*/

Find the number of Quarters between two dates

DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '1997-01-01',
    @d2 = '2009-12-31'
    
SELECT
    DATEDIFF(quarter, @d1, @d2) AS QuarterDiff
/*
QuarterDiff
-----------
51
*/

Find the number of Months between two dates

DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '2007-01-01',
    @d2 = '2009-12-31'
    
SELECT
    DATEDIFF(month, @d1, @d2) AS MonthDiff
/*
MonthDiff
-----------
35
*/

Find the number of Weeks between two dates

DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '2009-01-01',
    @d2 = '2009-12-31'
    
SELECT
    DATEDIFF(week, @d1, @d2) AS WeekDiff
/*
WeekDiff
-----------
52
*/

Find the number of Days between two dates

DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '2009-01-01',
    @d2 = '2009-12-31'
    
SELECT
    DATEDIFF(day, @d1, @d2) AS DaysDiff
/*
DaysDiff
-----------
364
*/

Find the number of Hours between two date/time values

DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '2009-01-01 10:15:23',
    @d2 = '2009-01-01 17:45:59'
    
SELECT
    DATEDIFF(hour, @d1, @d2) AS HourDiff
/*
HourDiff
-----------
7
*/

Find the number of Minutes between two date/time values

DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '2009-01-01 10:15:23',
    @d2 = '2009-01-01 17:45:59'
    
SELECT
    DATEDIFF(minute, @d1, @d2) AS MinuteDiff
/*
MinuteDiff
-----------
450
*/

Find the number of Seconds between two date/time values

DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '2009-01-01 10:15:23',
    @d2 = '2009-01-01 10:45:59'
    
SELECT
    DATEDIFF(second, @d1, @d2) AS SecondDiff
/*
SecondDiff
-----------
1836
*/

Find the number of Milliseconds between two date/time values

DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '2009-01-01 10:15:23',
    @d2 = '2009-01-01 10:15:59'
    
SELECT
    DATEDIFF(millisecond, @d1, @d2) AS MillisecondDiff
/*
MillisecondDiff
---------------
36000
*/

Share |
 Cant find the page you are looking for?
 Help us to improve by adding the content that you are looking for.
 Leave a feedback
 We look forward to hear your comments and feedback.