SQL Server/T-SQL Tutorial/Date Functions/DATEDIFF

A SELECT statement that computes the age of a Billing with DATEDIFF

6>
7>
8> create table Billings (
9>     BankerID           INTEGER,
10>     BillingNumber      INTEGER,
11>     BillingDate        datetime,
12>     BillingTotal       INTEGER,
13>     TermsID            INTEGER,
14>     BillingDueDate     datetime ,
15>     PaymentTotal       INTEGER,
16>     CreditTotal        INTEGER
17>
18> );
19> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2>
3>
4>
5> SELECT BillingDate,
6>     GETDATE() AS "Today""s Date",
7>     DATEDIFF(day, BillingDate, GETDATE()) AS Age
8> FROM Billings
9> GO
BillingDate             Today"s Date            Age
----------------------- ----------------------- -----------
2005-01-22 00:00:00.000 2008-08-11 21:35:09.873        1297
2001-02-21 00:00:00.000 2008-08-11 21:35:09.873        2728
2003-05-02 00:00:00.000 2008-08-11 21:35:09.873        1928
1999-03-12 00:00:00.000 2008-08-11 21:35:09.873        3440
2000-04-23 00:00:00.000 2008-08-11 21:35:09.873        3032
2001-06-14 00:00:00.000 2008-08-11 21:35:09.873        2615
2002-07-15 00:00:00.000 2008-08-11 21:35:09.873        2219
2003-08-16 00:00:00.000 2008-08-11 21:35:09.873        1822
2004-09-17 00:00:00.000 2008-08-11 21:35:09.873        1424
2005-10-18 00:00:00.000 2008-08-11 21:35:09.873        1028
(10 rows affected)
1>
2> drop table Billings;
3> GO

DATEDIFF returns the difference between two dates as specified by datepart: DATEDIFF(datepart,date1,date2)

4>
5> SELECT DATEDIFF(DY,"08/30/97","10/01/97")
6> GO
-----------
         32
(1 rows affected)
1>

Days between two dates

4>
5> CREATE TABLE titles(
6>    title_id       varchar(20),
7>    title          varchar(80)       NOT NULL,
8>    type           char(12)          NOT NULL,
9>    pub_id         char(4)               NULL,
10>    price          money                 NULL,
11>    advance        money                 NULL,
12>    royalty        int                   NULL,
13>    ytd_sales      int                   NULL,
14>    notes          varchar(200)          NULL,
15>    pubdate        datetime          NOT NULL
16> )
17> GO
1>
2> insert titles values ("1", "Secrets",   "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The",       "business",     "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology",   "0736", $7.99,  $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology",   "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With",      "business",     "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley",    "mod_cook",     "0877", $19.99, $0.00,    12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?",      "trad_cook",    "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty",     "trad_cook",    "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> SELECT *
4> FROM titles
5> WHERE
6> ABS(DATEDIFF(DAY, pubdate, "1997.09.30")) <= 150
7> GO
title_id             title                                                                            type         pub_id price                 advance               royalty     ytd_sales   notes
                                                                                                                                                                                               pubdate
-------------------- -------------------------------------------------------------------------------- ------------ ------ --------------------- --------------------- ----------- ----------- ----------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------
--------------
(0 rows affected)
1>
2> drop table titles;
3> GO
1>

Find difference in months between now and EndDate

14>
15> CREATE TABLE employee(
16>    id          INTEGER NOT NULL PRIMARY KEY,
17>    first_name  VARCHAR(10),
18>    last_name   VARCHAR(10),
19>    salary      DECIMAL(10,2),
20>    start_Date  DATETIME,
21>    region      VARCHAR(10),
22>    city        VARCHAR(20),
23>    managerid   INTEGER
24> );
25> GO
1> INSERT INTO employee VALUES (1, "Jason" ,  "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison",  "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" ,  "Smith",  6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" ,  "Rice",   5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert",  "Black",  4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" ,  "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" ,  "Larry",  5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" ,  "Cat",    4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan"  ,  "Act",    6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id          first_name last_name  salary       start_Date              region     city                 managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
          1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
          2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
          3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
          4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
          5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
          6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
          7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
          8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
          9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10
(9 rows affected)
1>
2>
3>
4> SELECT ID,start_Date,
5> DATEDIFF(m, Start_Date, GETDATE()) MonthsFromNow
6> FROM Employee
7> GO
ID          start_Date              MonthsFromNow
----------- ----------------------- -------------
          1 2005-03-22 00:00:00.000            31
          2 2003-07-21 00:00:00.000            51
          3 2001-12-01 00:00:00.000            70
          4 2006-03-03 00:00:00.000            19
          5 2004-07-02 00:00:00.000            39
          6 2002-05-19 00:00:00.000            65
          7 2008-03-18 00:00:00.000            -5
          8 2007-07-17 00:00:00.000             3
          9 2001-04-16 00:00:00.000            78
(9 rows affected)
1>
2>
3> drop table employee;
4> GO

Finding the Difference Between Two Dates

DATEDIFF subtracts the first date from the second date.
The syntax for DATEDIFF is:
DATEDIFF ( datepart , startdate , enddate )
The first datepart code uses the same datepart codes as DATEADD.
The second and third arguments are the date values that are part of the subtraction.

select DATEDIFF(day, "2001-12-01", "2002-09-30")

2>
3> select DATEDIFF(day, "2001-12-01", "2002-09-30")
4> GO
-----------
        303
(1 rows affected)

select DATEDIFF(day, "2002-09-30", "2001-12-01")

2>
3> select DATEDIFF(day, "2002-09-30", "2001-12-01")
4> GO
-----------
       -303
(1 rows affected)

select DATEDIFF(hour, "06:46:45", "11:35:00")

2>
3> select DATEDIFF(hour, "06:46:45", "11:35:00")
4> GO
-----------
          5
(1 rows affected)

select DATEDIFF(minute, "06:46:45", "11:35:00")

2>
3> select DATEDIFF(minute, "06:46:45", "11:35:00")
4> GO
-----------
        289
(1 rows affected)

select DATEDIFF(month, "2001-12-01", "2002-09-30")

2>
3> select DATEDIFF(month, "2001-12-01", "2002-09-30")
4> GO
-----------
          9
(1 rows affected)

select DATEDIFF(quarter, "2001-12-01", "2002-09-30")

2>
3> select DATEDIFF(quarter, "2001-12-01", "2002-09-30")
4> GO
-----------
          3
(1 rows affected)

select DATEDIFF(second, "06:46:45", "11:35:00")

2>
3> select DATEDIFF(second, "06:46:45", "11:35:00")
4> GO
-----------
      17295
(1 rows affected)

select DATEDIFF(week, "2001-12-01", "2002-09-30")

2>
3> select DATEDIFF(week, "2001-12-01", "2002-09-30")
4> GO
-----------
         44
(1 rows affected)

select DATEDIFF(year, "2001-12-01", "2002-09-30")

2>
3> select DATEDIFF(year, "2001-12-01", "2002-09-30")
4> GO
-----------
          1
(1 rows affected)

SELECT DATEDIFF(yy, "Dec", "Jan 1, 1999")

3> SELECT DATEDIFF(yy, "Dec 31, 1998", "Jan 1, 1999")
4> GO
-----------
          1
(1 rows affected)

SELECT DATEDIFF(yy, "Jan 1, 1998", "Dec")

3> SELECT DATEDIFF(yy, "Jan 1, 1998", "Dec 31, 1998")
4> GO
-----------
          0
(1 rows affected)
1>
2>