SQL Server/T-SQL Tutorial/Date Functions/DATEPART
Содержание
- 1 (DATEPART(dd, @date1) = DATEPART(dd, @date2))
- 2 DATEPART Function returns the integer value of the datepart specified: DATEPART(datepart,date)
- 3 (DATEPART(mm, @date1) = DATEPART(mm, @date2))
- 4 DATEPART returns part of a date as an integer value.
- 5 (DATEPART(yy, @date1) = DATEPART(yy, @date2)
- 6 Grouping by the Week by using datepart function
- 7 Identifying Weekday
- 8 IF DATEDIFF(dd, "7/5/99","7/5/00") = 0
- 9 select DATEPART(day, "2002-35:00")
- 10 select DATEPART(dayofyear, "2002-35:00")
- 11 SELECT DATEPART(dd, "7/5/00")
- 12 SELECT DATEPART(dd, "7/5/99")
- 13 select DATEPART(hour, "2002-35:00")
- 14 select DATEPART(m,"2002-09-30")
- 15 select DATEPART(minute, "2002-35:00")
- 16 select DATEPART(month, "2002-35:00")
- 17 select DATEPART(quarter, "2002-35:00")
- 18 select DATEPART(second, "2002-35:00")
- 19 select DATEPART(week, "2002-35:00")
- 20 select DATEPART(weekday, "2002-35:00")
- 21 select DATEPART(year, "2002-35:00")
- 22 Values for the datepart parameter.
- 23 where DATEDIFF(WEEK, pubdate, GETDATE())
- 24 where DATEPART(DAY, pubdate) BETWEEN 8 AND 14
- 25 WHERE DATEPART(WEEKDAY, pubdate) = 3
(DATEPART(dd, @date1) = DATEPART(dd, @date2))
3> IF (DATEPART(mm, @date1) = DATEPART(mm, @date2)) AND
4> (DATEPART(dd, @date1) = DATEPART(dd, @date2)) AND
5> (DATEPART(yy, @date1) = DATEPART(yy, @date2))
6> PRINT "The dates are the same"
7> GO
DATEPART Function returns the integer value of the datepart specified: DATEPART(datepart,date)
4>
5> SELECT DATEPART(MM,"Oct 31 1997")
6> GO
-----------
10
(1 rows affected)
1>
(DATEPART(mm, @date1) = DATEPART(mm, @date2))
3> IF (DATEPART(mm, @date1) = DATEPART(mm, @date2)) AND
4> (DATEPART(dd, @date1) = DATEPART(dd, @date2)) AND
5> (DATEPART(yy, @date1) = DATEPART(yy, @date2))
6> PRINT "The dates are the same"
7> GO
DATEPART returns part of a date as an integer value.
The syntax for DATEPART is: DATEPART(part_of_the_date_to_return, the_date)
19>
20> SELECT DATEPART(dw,GETDATE())
21> GO
-----------
1
(1 rows affected)
(DATEPART(yy, @date1) = DATEPART(yy, @date2)
3> IF (DATEPART(mm, @date1) = DATEPART(mm, @date2)) AND
4> (DATEPART(dd, @date1) = DATEPART(dd, @date2)) AND
5> (DATEPART(yy, @date1) = DATEPART(yy, @date2))
6> PRINT "The dates are the same"
7> GO
Grouping by the Week by using datepart function
4>
5>
6> CREATE TABLE Orders (
7> OrderID int IDENTITY (1, 1) NOT NULL ,
8> CustomerID nchar (5) NULL ,
9> EmployeeID int NULL ,
10> OrderDate datetime NULL ,
11> RequiredDate datetime NULL ,
12> ShippedDate datetime NULL ,
13> ShipVia int NULL ,
14> Freight money NULL DEFAULT (0),
15> ShipName nvarchar (40) NULL ,
16> ShipAddress nvarchar (60) NULL ,
17> ShipCity nvarchar (15) NULL ,
18> ShipRegion nvarchar (15) NULL ,
19> ShipPostalCode nvarchar (10) NULL ,
20> ShipCountry nvarchar (15) NULL
21> )
22> GO
1>
2>
3> SELECT od - wd + 1 AS week_start, od + 7 - wd AS week_end,
4> COUNT(*) AS numorders
5> FROM (SELECT OrderID AS oid, OrderDate AS od,
6> DATEPART(weekday, OrderDate + @@DATEFIRST - 1) AS wd
7> FROM dbo.Orders) AS D
8> GROUP BY od - wd + 1, od + 7 - wd;
9> GO
week_start week_end numorders
----------------------- ----------------------- -----------
(0 rows affected)
1>
2> drop table Orders;
3> GO
1>
2>
Identifying Weekday
4> CREATE TABLE Orders (
5> OrderID int IDENTITY (1, 1) NOT NULL ,
6> CustomerID nchar (5) NULL ,
7> EmployeeID int NULL ,
8> OrderDate datetime NULL ,
9> RequiredDate datetime NULL ,
10> ShippedDate datetime NULL ,
11> ShipVia int NULL ,
12> Freight money NULL DEFAULT (0),
13> ShipName nvarchar (40) NULL ,
14> ShipAddress nvarchar (60) NULL ,
15> ShipCity nvarchar (15) NULL ,
16> ShipRegion nvarchar (15) NULL ,
17> ShipPostalCode nvarchar (10) NULL ,
18> ShipCountry nvarchar (15) NULL
19> )
20> GO
1>
2>
3>
4> SELECT OrderID, OrderDate
5> FROM dbo.Orders
6> WHERE DATEDIFF(day, "19000102", OrderDate) % 7 = 0;
7> GO
OrderID OrderDate
----------- -----------------------
(0 rows affected)
1>
2> drop table Orders;
3> GO
1>
IF DATEDIFF(dd, "7/5/99","7/5/00") = 0
3> IF DATEDIFF(dd, "7/5/99","7/5/00") = 0
4> PRINT "The dates are the same"
5> ELSE PRINT "The dates are different"
6> GO
The dates are different
1>
select DATEPART(day, "2002-35:00")
3> select DATEPART(day, "2002-09-30 11:35:00")
4> GO
-----------
30
(1 rows affected)
1>
select DATEPART(dayofyear, "2002-35:00")
3> select DATEPART(dayofyear, "2002-09-30 11:35:00")
4> GO
-----------
273
(1 rows affected)
1>
SELECT DATEPART(dd, "7/5/00")
1> SELECT DATEPART(dd, "7/5/00")
2> GO
-----------
7
(1 rows affected)
SELECT DATEPART(dd, "7/5/99")
3> SELECT DATEPART(dd, "7/5/99")
4> GO
-----------
7
(1 rows affected)
select DATEPART(hour, "2002-35:00")
2> select DATEPART(hour, "2002-09-30 11:35:00")
3> GO
-----------
11
(1 rows affected)
1>
select DATEPART(m,"2002-09-30")
2> select DATEPART(m,"2002-09-30")
3> GO
-----------
9
(1 rows affected)
1>
select DATEPART(minute, "2002-35:00")
3> select DATEPART(minute, "2002-09-30 11:35:00")
4> GO
-----------
35
(1 rows affected)
1>
select DATEPART(month, "2002-35:00")
3> select DATEPART(month, "2002-09-30 11:35:00")
4> GO
-----------
9
(1 rows affected)
select DATEPART(quarter, "2002-35:00")
3> select DATEPART(quarter, "2002-09-30 11:35:00")
4> GO
-----------
3
(1 rows affected)
1>
select DATEPART(second, "2002-35:00")
3> select DATEPART(second, "2002-09-30 11:35:00")
4> GO
-----------
0
(1 rows affected)
1>
select DATEPART(week, "2002-35:00")
3> select DATEPART(week, "2002-09-30 11:35:00")
4> GO
-----------
40
(1 rows affected)
select DATEPART(weekday, "2002-35:00")
2> select DATEPART(weekday, "2002-09-30 11:35:00")
3> GO
-----------
2
(1 rows affected)
select DATEPART(year, "2002-35:00")
2> select DATEPART(year, "2002-09-30 11:35:00")
3> GO
-----------
2002
(1 rows affected)
Values for the datepart parameter.
This function returns an integer value for the part of a date specified in the date part selection.
The syntax for DATEPART is:
DATEPART ( datepart , date )
The second parameter, date, designates the date for which the integer value is calculated.
datepart (Full Name) Abbreviation Values
year Yy 1753-9999
quarter Qq 1-4
month Mm 1-12
dayofyear Dy 1-366
day Dd 1-31
week Wk 1-53
weekday Dw 1-7 (Sunday-Saturday)
hour Hh 0-23
minute Mi 0-59
second Ss 0-59
millisecond Ms 0-999
where DATEDIFF(WEEK, pubdate, GETDATE())
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
where DATEPART(DAY, pubdate) BETWEEN 8 AND 14
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 where DATEPART(DAY, pubdate) BETWEEN 8 AND 14
4> GO
title_id title type pub_id price advance royalty ytd_sales notes
pubdate
-------------------- -------------------------------------------------------------------------------- ------------ ------ --------------------- --------------------- ----------- ----------- ----------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------
--------------
(0 rows affected)
1>
2> drop table titles;
3> GO
WHERE DATEPART(WEEKDAY, pubdate) = 3
31> CREATE TABLE titles(
32> title_id varchar(20),
33> title varchar(80) NOT NULL,
34> type char(12) NOT NULL,
35> pub_id char(4) NULL,
36> price money NULL,
37> advance money NULL,
38> royalty int NULL,
39> ytd_sales int NULL,
40> notes varchar(200) NULL,
41> pubdate datetime NOT NULL
42> )
43> 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> DATEPART(WEEKDAY, pubdate) = 3
7> GO
title_id title type pub_id price advance royalty ytd_sales notes
pubdate
-------------------- -------------------------------------------------------------------------------- ------------ ------ --------------------- --------------------- ----------- ----------- ----------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------
--------------
1 Secrets popular_comp 1389 20.0000 8000.0000 10 4095 Note 1
1994-12-0
6 00:00:00.000
(1 rows affected)
1>
2> drop table titles;
3> GO