SQL Server/T-SQL Tutorial/Date Functions/DATEDIFF
Содержание
- 1 A SELECT statement that computes the age of a Billing with DATEDIFF
- 2 DATEDIFF returns the difference between two dates as specified by datepart: DATEDIFF(datepart,date1,date2)
- 3 Days between two dates
- 4 Find difference in months between now and EndDate
- 5 Finding the Difference Between Two Dates
- 6 select DATEDIFF(day, "2001-12-01", "2002-09-30")
- 7 select DATEDIFF(day, "2002-09-30", "2001-12-01")
- 8 select DATEDIFF(hour, "06:46:45", "11:35:00")
- 9 select DATEDIFF(minute, "06:46:45", "11:35:00")
- 10 select DATEDIFF(month, "2001-12-01", "2002-09-30")
- 11 select DATEDIFF(quarter, "2001-12-01", "2002-09-30")
- 12 select DATEDIFF(second, "06:46:45", "11:35:00")
- 13 select DATEDIFF(week, "2001-12-01", "2002-09-30")
- 14 select DATEDIFF(year, "2001-12-01", "2002-09-30")
- 15 SELECT DATEDIFF(yy, "Dec", "Jan 1, 1999")
- 16 SELECT DATEDIFF(yy, "Jan 1, 1998", "Dec")
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>