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

Материал из SQL эксперт
Версия от 13:23, 26 мая 2010; Admin (обсуждение | вклад) (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>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Date_Functions/DATEPART&oldid=6455»