в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:23:08Z

1 версия

Новая страница

== (DATEPART(dd, @date1) = DATEPART(dd, @date2))==



<source lang="sql">
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</source>

== DATEPART Function returns the integer value of the datepart specified: DATEPART(datepart,date)==



<source lang="sql">
4>
5> SELECT DATEPART(MM,"Oct 31 1997")
6> GO
-----------
10
(1 rows affected)
1></source>

== (DATEPART(mm, @date1) = DATEPART(mm, @date2))==



<source lang="sql">
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</source>

== DATEPART returns part of a date as an integer value.==



<source lang="sql">
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)</source>

== (DATEPART(yy, @date1) = DATEPART(yy, @date2)==



<source lang="sql">
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</source>

== Grouping by the Week by using datepart function==



<source lang="sql">
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></source>

== Identifying Weekday==



<source lang="sql">
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></source>

== IF DATEDIFF(dd, "7/5/99","7/5/00") = 0==



<source lang="sql">
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></source>

== select DATEPART(day, "2002-35:00")==



<source lang="sql">
3> select DATEPART(day, "2002-09-30 11:35:00")
4> GO
-----------
30
(1 rows affected)
1></source>

== select DATEPART(dayofyear, "2002-35:00")==



<source lang="sql">
3> select DATEPART(dayofyear, "2002-09-30 11:35:00")
4> GO
-----------
273
(1 rows affected)
1></source>

== SELECT DATEPART(dd, "7/5/00")==



<source lang="sql">
1> SELECT DATEPART(dd, "7/5/00")
2> GO
-----------
7
(1 rows affected)</source>

== SELECT DATEPART(dd, "7/5/99")==



<source lang="sql">
3> SELECT DATEPART(dd, "7/5/99")
4> GO
-----------
7
(1 rows affected)</source>

== select DATEPART(hour, "2002-35:00")==



<source lang="sql">
2> select DATEPART(hour, "2002-09-30 11:35:00")
3> GO
-----------
11
(1 rows affected)
1></source>

== select DATEPART(m,"2002-09-30")==



<source lang="sql">
2> select DATEPART(m,"2002-09-30")
3> GO
-----------
9
(1 rows affected)
1></source>

== select DATEPART(minute, "2002-35:00")==



<source lang="sql">
3> select DATEPART(minute, "2002-09-30 11:35:00")
4> GO
-----------
35
(1 rows affected)
1></source>

== select DATEPART(month, "2002-35:00")==



<source lang="sql">
3> select DATEPART(month, "2002-09-30 11:35:00")
4> GO
-----------
9
(1 rows affected)</source>

== select DATEPART(quarter, "2002-35:00")==



<source lang="sql">
3> select DATEPART(quarter, "2002-09-30 11:35:00")
4> GO
-----------
3
(1 rows affected)
1></source>

== select DATEPART(second, "2002-35:00")==



<source lang="sql">
3> select DATEPART(second, "2002-09-30 11:35:00")
4> GO
-----------
0
(1 rows affected)
1></source>

== select DATEPART(week, "2002-35:00")==



<source lang="sql">
3> select DATEPART(week, "2002-09-30 11:35:00")
4> GO
-----------
40
(1 rows affected)</source>

== select DATEPART(weekday, "2002-35:00")==



<source lang="sql">
2> select DATEPART(weekday, "2002-09-30 11:35:00")
3> GO
-----------
2
(1 rows affected)</source>

== select DATEPART(year, "2002-35:00")==



<source lang="sql">
2> select DATEPART(year, "2002-09-30 11:35:00")
3> GO
-----------
2002
(1 rows affected)</source>

== Values for the datepart parameter.==



<source lang="sql">
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</source>

== where DATEDIFF(WEEK, pubdate, GETDATE()) ==



<source lang="sql">
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</source>

== where DATEPART(DAY, pubdate) BETWEEN 8 AND 14==



<source lang="sql">
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</source>

== WHERE DATEPART(WEEKDAY, pubdate) = 3==



<source lang="sql">
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</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/Date Functions/DATEPART - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия