Cari Blog Ini

23 Juli 2012

Example of SQL Server Date Difference Calculation - WEEK

1. Find the number of WEEK between two dates with format Date - yyyy-mm-dd
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '2011-01-01',
    @d2 = '2012-12-31'
   
SELECT
    DATEDIFF(week, @d1, @d2) AS WeekDiff
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
WeekDiff
-----------
105

(1 row(s) affected)



2. Find the number of WEEK between two dates with format Date - mm/dd/yyyy
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '1/1/2011',
    @d2 = '12/31/2012'
   
SELECT
    DATEDIFF(week, @d1, @d2) AS WeekDiff
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
WeekDiff
-----------
105

(1 row(s) affected)



3. Find the number of WEEK between two dates with format Date - dd-mmm-yyyy
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '01-Jan-2011',
    @d2 = '31-Dec-2012'
   
SELECT
    DATEDIFF(week, @d1, @d2) AS WeekDiff
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
WeekDiff
-----------
105

(1 row(s) affected)


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