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

   <source lang="sql">

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</source>

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

   <source lang="sql">

4> 5> SELECT DATEDIFF(DY,"08/30/97","10/01/97") 6> GO

(1 rows affected) 1></source>

Days between two dates

   <source lang="sql">

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></source>

Find difference in months between now and EndDate

   <source lang="sql">

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</source>

Finding the Difference Between Two Dates

   <source lang="sql">

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.</source>

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

   <source lang="sql">

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

(1 rows affected)</source>

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

   <source lang="sql">

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

      -303

(1 rows affected)</source>

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

   <source lang="sql">

2> 3> select DATEDIFF(hour, "06:46:45", "11:35:00") 4> GO

(1 rows affected)</source>

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

   <source lang="sql">

2> 3> select DATEDIFF(minute, "06:46:45", "11:35:00") 4> GO

(1 rows affected)</source>

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

   <source lang="sql">

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

(1 rows affected)</source>

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

   <source lang="sql">

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

(1 rows affected)</source>

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

   <source lang="sql">

2> 3> select DATEDIFF(second, "06:46:45", "11:35:00") 4> GO

(1 rows affected)</source>

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

   <source lang="sql">

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

(1 rows affected)</source>

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

   <source lang="sql">

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

(1 rows affected)</source>

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

   <source lang="sql">

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

(1 rows affected)</source>

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

   <source lang="sql">

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

(1 rows affected) 1> 2></source>

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

Содержание

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

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

Days between two dates

Find difference in months between now and EndDate

Finding the Difference Between Two Dates

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

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

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

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

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

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

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

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

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

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

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

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты