Days between two dates : DATEDIFF « Date Functions « SQL Server / T-SQL Tutorial






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>








10.4.DATEDIFF
10.4.1.Finding the Difference Between Two Dates
10.4.2.DATEDIFF returns the difference between two dates as specified by datepart: DATEDIFF(datepart,date1,date2)
10.4.3.select DATEDIFF(day, '2001-12-01', '2002-09-30')
10.4.4.select DATEDIFF(month, '2001-12-01', '2002-09-30')
10.4.5.select DATEDIFF(year, '2001-12-01', '2002-09-30')
10.4.6.select DATEDIFF(hour, '06:46:45', '11:35:00')
10.4.7.select DATEDIFF(minute, '06:46:45', '11:35:00')
10.4.8.select DATEDIFF(second, '06:46:45', '11:35:00')
10.4.9.select DATEDIFF(quarter, '2001-12-01', '2002-09-30')
10.4.10.select DATEDIFF(week, '2001-12-01', '2002-09-30')
10.4.11.select DATEDIFF(day, '2002-09-30', '2001-12-01')
10.4.12.SELECT DATEDIFF(yy, 'Jan 1, 1998', 'Dec 31, 1998')
10.4.13.SELECT DATEDIFF(yy, 'Dec 31, 1998', 'Jan 1, 1999')
10.4.14.Find difference in months between now and EndDate
10.4.15.A SELECT statement that computes the age of a Billing with DATEDIFF
10.4.16.Days between two dates