23 Juli 2012

Example of SQL Server Date Difference Calculation - HOUR

1. Find the number of HOUR between two dates with format Date - yyyy-mm-dd
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '2011-01-01 00:00:01',
    @d2 = '2011-01-02 23:59:00'
   
SELECT
    DATEDIFF(hour, @d1, @d2) AS HourDiff
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
HourDiff
-----------
47

(1 row(s) affected)



2. Find the number of HOUR between two dates with format Date - mm/dd/yyyy
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '1/1/2011 00:00:01',
    @d2 = '1/2/2011 23:59:00'
   
SELECT
    DATEDIFF(hour, @d1, @d2) AS HourDiff
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
HourDiff
-----------
47

(1 row(s) affected)



3. Find the number of HOUR between two dates with format Date - dd-mmm-yyyy
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '01-Jan-2011 00:00:01',
    @d2 = '02-Jan-2011 23:59:00'
   
SELECT
    DATEDIFF(hour, @d1, @d2) AS HourDiff
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
HourDiff
-----------
47

(1 row(s) affected)


Source:
http://syntaxhelp.com/SQLServer/DATEDIFF