where DATEDIFF(WEEK, pubdate, GETDATE()) <= 48 : DATEPART « Date Functions « SQL Server / T-SQL Tutorial





3> CREATE TABLE titles(
4>    title_id       varchar(20),
5>    title          varchar(80)       NOT NULL,
6>    type           char(12)          NOT NULL,
7>    pub_id         char(4)               NULL,
8>    price          money                 NULL,
9>    advance        money                 NULL,
10>    royalty        int                   NULL,
11>    ytd_sales      int                   NULL,
12>    notes          varchar(200)          NULL,
13>    pubdate        datetime          NOT NULL
14> )
15> 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 * from titles
4> where DATEDIFF(WEEK, pubdate, GETDATE()) <= 48
5> GO
title_id             title                                                                            type         pub_id price                 advance               royalty     ytd_sales   notes
                                                                                                                                                                                               pubdate

-------------------- -------------------------------------------------------------------------------- ------------ ------ --------------------- --------------------- ----------- ----------- ----------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------
--------------

(0 rows affected)
1>
2> drop table titles;
3> GO










10.8.DATEPART
10.8.1.DATEPART returns part of a date as an integer value.
10.8.2.Values for the datepart parameter.
10.8.3.WHERE DATEPART(WEEKDAY, pubdate) = 3
10.8.4.where DATEPART(DAY, pubdate) BETWEEN 8 AND 14
10.8.5.where DATEDIFF(WEEK, pubdate, GETDATE()) <= 48
10.8.6.DATEPART Function returns the integer value of the datepart specified: DATEPART(datepart,date)
10.8.7.select DATEPART(day, '2002-09-30 11:35:00')
10.8.8.select DATEPART(month, '2002-09-30 11:35:00')
10.8.9.select DATEPART(year, '2002-09-30 11:35:00')
10.8.10.select DATEPART(hour, '2002-09-30 11:35:00')
10.8.11.select DATEPART(minute, '2002-09-30 11:35:00')
10.8.12.select DATEPART(second, '2002-09-30 11:35:00')
10.8.13.select DATEPART(quarter, '2002-09-30 11:35:00')
10.8.14.select DATEPART(dayofyear, '2002-09-30 11:35:00')
10.8.15.select DATEPART(week, '2002-09-30 11:35:00')
10.8.16.select DATEPART(weekday, '2002-09-30 11:35:00')
10.8.17.select DATEPART(m,'2002-09-30')
10.8.18.SELECT DATEPART(dd, '7/5/99')
10.8.19.SELECT DATEPART(dd, '7/5/00')
10.8.20.IF DATEDIFF(dd, '7/5/99','7/5/00') = 0
10.8.21.(DATEPART(mm, @date1) = DATEPART(mm, @date2))
10.8.22.(DATEPART(dd, @date1) = DATEPART(dd, @date2))
10.8.23.(DATEPART(yy, @date1) = DATEPART(yy, @date2)
10.8.24.Identifying Weekday
10.8.25.Grouping by the Week by using datepart function