SQL Server/T-SQL Tutorial/Data Types/datetime

Материал из SQL эксперт
Версия от 13:25, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

A BETWEEN phrase with literal values

   <source lang="sql">

4> 5> create table Billings ( 6> BankerID INTEGER, 7> BillingNumber INTEGER, 8> BillingDate datetime, 9> BillingTotal INTEGER, 10> TermsID INTEGER, 11> BillingDueDate datetime , 12> PaymentTotal INTEGER, 13> CreditTotal INTEGER 14> 15> ); 16> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> select * from Billings WHERE BillingDate BETWEEN "2002-05-01" AND "2002-05-31" 4> GO BankerID BillingNumber BillingDate BillingTotal TermsID BillingDueDate PaymentTotal CreditTotal

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

(0 rows affected) 1> 2> 3> drop table Billings; 4> GO 1></source>


Add integer and float number to a date value

   <source lang="sql">

4> create table Billings ( 5> BankerID INTEGER, 6> BillingNumber INTEGER, 7> BillingDate datetime, 8> BillingTotal INTEGER, 9> TermsID INTEGER, 10> BillingDueDate datetime , 11> PaymentTotal INTEGER, 12> CreditTotal INTEGER 13> 14> ); 15> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> SELECT 4> BillingDate + 9 "BillingDate + 9", 5> BillingDate + .5 "BillingDate + .5" 6> FROM Billings 7> GO BillingDate + 9 BillingDate + .5

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

2005-01-31 00:00:00.000 2005-01-22 12:00:00.000 2001-03-02 00:00:00.000 2001-02-21 12:00:00.000 2003-05-11 00:00:00.000 2003-05-02 12:00:00.000 1999-03-21 00:00:00.000 1999-03-12 12:00:00.000 2000-05-02 00:00:00.000 2000-04-23 12:00:00.000 2001-06-23 00:00:00.000 2001-06-14 12:00:00.000 2002-07-24 00:00:00.000 2002-07-15 12:00:00.000 2003-08-25 00:00:00.000 2003-08-16 12:00:00.000 2004-09-26 00:00:00.000 2004-09-17 12:00:00.000 2005-10-27 00:00:00.000 2005-10-18 12:00:00.000 (10 rows affected) 1> 2> 3> drop table Billings; 4> GO</source>


An UPDATE statement that assigns new values to datetime column with string value

   <source lang="sql">

2> 3> create table Billings ( 4> BankerID INTEGER, 5> BillingNumber INTEGER, 6> BillingDate datetime, 7> BillingTotal INTEGER, 8> TermsID INTEGER, 9> BillingDueDate datetime , 10> PaymentTotal INTEGER, 11> CreditTotal INTEGER 12> 13> ); 14> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 4> GO (1 rows affected) 1> 2> 3> 4> UPDATE Billings 5> SET BillingDate = "2002-09-21", 6> PaymentTotal = 19351.18 7> WHERE BillingNumber = "2" 8> GO (1 rows affected) 1> 2> 3> drop table Billings; 4> GO</source>


A SELECT statement that ignores date values

   <source lang="sql">

4> 5> create table Billings ( 6> BankerID INTEGER, 7> BillingNumber INTEGER, 8> BillingDate datetime, 9> BillingTotal INTEGER, 10> TermsID INTEGER, 11> BillingDueDate datetime , 12> PaymentTotal INTEGER, 13> CreditTotal INTEGER 14> 15> ); 16> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> SELECT *, CAST(CONVERT(char(12), BillingDueDate, 8) AS datetime) AS TimeOnly 3> FROM Billings 4> WHERE CAST(CONVERT(char(12), BillingDueDate, 8) AS datetime) >= "09:00:00" AND 5> CAST(CONVERT(char(12), BillingDueDate, 8) AS datetime) < "12:59:59:999" 6> 7> GO BankerID BillingNumber BillingDate BillingTotal TermsID BillingDueDate PaymentTotal CreditTotal TimeOnly

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

(0 rows affected) 1> 2> drop table Billings; 3> GO 1></source>


A SELECT statement that searches for month, day, and year components

   <source lang="sql">

5> 6> create table Billings ( 7> BankerID INTEGER, 8> BillingNumber INTEGER, 9> BillingDate datetime, 10> BillingTotal INTEGER, 11> TermsID INTEGER, 12> BillingDueDate datetime , 13> PaymentTotal INTEGER, 14> CreditTotal INTEGER 15> 16> ); 17> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> SELECT * FROM Billings 2> WHERE MONTH(BillingDueDate) = 2 AND DAY(BillingDueDate) = 28 3> AND YEAR(BillingDueDate) = 1992 4> 5> 6> GO BankerID BillingNumber BillingDate BillingTotal TermsID BillingDueDate PaymentTotal CreditTotal

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

(0 rows affected) 1> 2> drop table Billings; 3> GO 1></source>


A SELECT statement that uses the CAST function to remove time values

   <source lang="sql">

4> 5> create table Billings ( 6> BankerID INTEGER, 7> BillingNumber INTEGER, 8> BillingDate datetime, 9> BillingTotal INTEGER, 10> TermsID INTEGER, 11> BillingDueDate datetime , 12> PaymentTotal INTEGER, 13> CreditTotal INTEGER 14> 15> ); 16> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> SELECT *, CAST(CAST(BillingDueDate AS char(11)) AS datetime) AS DateOnly 4> FROM Billings 5> WHERE CAST(CAST(BillingDueDate AS char(11)) AS datetime) = "1992-02-28" 6> 7> GO BankerID BillingNumber BillingDate BillingTotal TermsID BillingDueDate PaymentTotal CreditTotal DateOnly

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

(0 rows affected) 1> 2> drop table Billings; 3> GO</source>


A SELECT statement that uses the CONVERT function to remove time values

   <source lang="sql">

4> 5> 6> create table Billings ( 7> BankerID INTEGER, 8> BillingNumber INTEGER, 9> BillingDate datetime, 10> BillingTotal INTEGER, 11> TermsID INTEGER, 12> BillingDueDate datetime , 13> PaymentTotal INTEGER, 14> CreditTotal INTEGER 15> 16> ); 17> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> SELECT *, CAST(CONVERT(char(10), BillingDueDate, 110) AS datetime) AS DateOnly 4> FROM Billings 5> WHERE CAST(CONVERT(char(10), BillingDueDate, 110) AS datetime) = "1992-02-28" 6> 7> GO BankerID BillingNumber BillingDate BillingTotal TermsID BillingDueDate PaymentTotal CreditTotal DateOnly

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

(0 rows affected) 1> 2> drop table Billings; 3> GO 1></source>


Between date

   <source lang="sql">

3> IF OBJECT_ID("dbo.Sessions") IS NOT NULL 4> DROP TABLE dbo.Sessions; 5> GO 1> 2> CREATE TABLE dbo.Sessions 3> ( 4> keycol INT NOT NULL IDENTITY PRIMARY KEY, 5> app VARCHAR(10) NOT NULL, 6> usr VARCHAR(10) NOT NULL, 7> starttime DATETIME NOT NULL, 8> endtime DATETIME NOT NULL, 9> CHECK(endtime > starttime) 10> ); 11> GO 1> 2> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 08:30", "20060212 10:30"); 3> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 08:30", "20060212 08:45"); 4> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 09:00", "20060212 09:30"); 5> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 09:15", "20060212 10:30"); 6> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 09:15", "20060212 09:30"); 7> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 10:30", "20060212 14:30"); 8> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 10:45", "20060212 11:30"); 9> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 11:00", "20060212 12:30"); 10> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 08:30", "20060212 08:45"); 11> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 09:00", "20060212 09:30"); 12> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 11:45", "20060212 12:00"); 13> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 12:30", "20060212 14:00"); 14> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 12:45", "20060212 13:30"); 15> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 13:00", "20060212 14:00"); 16> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 14:00", "20060212 16:30"); 17> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 15:30", "20060212 17:00"); 18> GO 1> 2> SELECT S1.app, S1.usr, 3> S1.keycol AS key1, S1.starttime AS start1, S1.endtime AS end1, 4> S2.keycol AS key2, S2.starttime AS start2, S2.endtime AS end2 5> FROM dbo.Sessions AS S1 6> JOIN dbo.Sessions AS S2 7> ON S2.app = S1.app 8> AND S2.usr = S1.usr 9> AND (S2.starttime BETWEEN S1.starttime AND S1.endtime 10> OR S1.starttime BETWEEN S2.starttime AND S2.endtime); 11> GO app usr key1 start1 end1 key2 start2 end2

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

app1 user1 1 2006-02-12 08:30:00.000 2006-02-12 10:30:00.000 1 2006-02-12 08:30:00.000 2006-02-12 10:30:00.000 app1 user1 3 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000 1 2006-02-12 08:30:00.000 2006-02-12 10:30:00.000 app1 user1 5 2006-02-12 09:15:00.000 2006-02-12 09:30:00.000 1 2006-02-12 08:30:00.000 2006-02-12 10:30:00.000 app1 user2 2 2006-02-12 08:30:00.000 2006-02-12 08:45:00.000 2 2006-02-12 08:30:00.000 2006-02-12 08:45:00.000 app1 user1 1 2006-02-12 08:30:00.000 2006-02-12 10:30:00.000 3 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000 app1 user1 3 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000 3 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000 app1 user1 5 2006-02-12 09:15:00.000 2006-02-12 09:30:00.000 3 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000 app1 user2 4 2006-02-12 09:15:00.000 2006-02-12 10:30:00.000 4 2006-02-12 09:15:00.000 2006-02-12 10:30:00.000 app1 user2 6 2006-02-12 10:30:00.000 2006-02-12 14:30:00.000 4 2006-02-12 09:15:00.000 2006-02-12 10:30:00.000 app1 user1 1 2006-02-12 08:30:00.000 2006-02-12 10:30:00.000 5 2006-02-12 09:15:00.000 2006-02-12 09:30:00.000 app1 user1 3 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000 5 2006-02-12 09:15:00.000 2006-02-12 09:30:00.000 app1 user1 5 2006-02-12 09:15:00.000 2006-02-12 09:30:00.000 5 2006-02-12 09:15:00.000 2006-02-12 09:30:00.000 app1 user2 4 2006-02-12 09:15:00.000 2006-02-12 10:30:00.000 6 2006-02-12 10:30:00.000 2006-02-12 14:30:00.000 app1 user2 6 2006-02-12 10:30:00.000 2006-02-12 14:30:00.000 6 2006-02-12 10:30:00.000 2006-02-12 14:30:00.000 app1 user2 8 2006-02-12 11:00:00.000 2006-02-12 12:30:00.000 6 2006-02-12 10:30:00.000 2006-02-12 14:30:00.000 app1 user1 7 2006-02-12 10:45:00.000 2006-02-12 11:30:00.000 7 2006-02-12 10:45:00.000 2006-02-12 11:30:00.000 app1 user2 6 2006-02-12 10:30:00.000 2006-02-12 14:30:00.000 8 2006-02-12 11:00:00.000 2006-02-12 12:30:00.000 app1 user2 8 2006-02-12 11:00:00.000 2006-02-12 12:30:00.000 8 2006-02-12 11:00:00.000 2006-02-12 12:30:00.000 app2 user1 9 2006-02-12 08:30:00.000 2006-02-12 08:45:00.000 9 2006-02-12 08:30:00.000 2006-02-12 08:45:00.000 app2 user2 10 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000 10 2006-02-12 09:00:00.000 2006-02-12 09:30:00.000 app2 user1 11 2006-02-12 11:45:00.000 2006-02-12 12:00:00.000 11 2006-02-12 11:45:00.000 2006-02-12 12:00:00.000 app2 user2 12 2006-02-12 12:30:00.000 2006-02-12 14:00:00.000 12 2006-02-12 12:30:00.000 2006-02-12 14:00:00.000 app2 user2 14 2006-02-12 13:00:00.000 2006-02-12 14:00:00.000 12 2006-02-12 12:30:00.000 2006-02-12 14:00:00.000 app2 user1 13 2006-02-12 12:45:00.000 2006-02-12 13:30:00.000 13 2006-02-12 12:45:00.000 2006-02-12 13:30:00.000 app2 user2 12 2006-02-12 12:30:00.000 2006-02-12 14:00:00.000 14 2006-02-12 13:00:00.000 2006-02-12 14:00:00.000 app2 user2 14 2006-02-12 13:00:00.000 2006-02-12 14:00:00.000 14 2006-02-12 13:00:00.000 2006-02-12 14:00:00.000 app2 user1 15 2006-02-12 14:00:00.000 2006-02-12 16:30:00.000 15 2006-02-12 14:00:00.000 2006-02-12 16:30:00.000 app2 user2 16 2006-02-12 15:30:00.000 2006-02-12 17:00:00.000 16 2006-02-12 15:30:00.000 2006-02-12 17:00:00.000 1> 2> drop table sessions; 3> GO 1> 2></source>


Billings on or before a specified date

   <source lang="sql">

4> 5> 6> create table Billings ( 7> BankerID INTEGER, 8> BillingNumber INTEGER, 9> BillingDate datetime, 10> BillingTotal INTEGER, 11> TermsID INTEGER, 12> BillingDueDate datetime , 13> PaymentTotal INTEGER, 14> CreditTotal INTEGER 15> 16> ); 17> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> select * from Billings WHERE BillingDate <= "2002-05-31" 4> GO BankerID BillingNumber BillingDate BillingTotal TermsID BillingDueDate PaymentTotal CreditTotal

------------- ----------------------- ------------ ----------- ----------------------- ------------ ----------- 
         2             2 2001-02-21 00:00:00.000          165           1 2002-02-22 00:00:00.000          123         321
         4             4 1999-03-12 00:00:00.000          165           1 2005-04-18 00:00:00.000          123         321
         5             5 2000-04-23 00:00:00.000          165           1 2005-04-17 00:00:00.000          123         321
         6             6 2001-06-14 00:00:00.000          165           1 2005-04-18 00:00:00.000          123         321

(4 rows affected) 1> drop table Billings; 2> GO</source>


CAST(OrderDate AS varchar)

   <source lang="sql">

3> 4> CREATE TABLE Orders ( 5> OrderID int 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> SELECT OrderDate, CAST(OrderDate AS varchar) AS "Converted" 2> FROM Orders 3> WHERE OrderID = 11050 4> GO OrderDate Converted

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

(0 rows affected) 1> 2> drop table orders; 3> GO 1></source>


datetime type

   <source lang="sql">

4> 5> 6> create table Billings ( 7> BankerID INTEGER, 8> BillingNumber INTEGER, 9> BillingDate datetime, 10> BillingTotal INTEGER, 11> TermsID INTEGER, 12> BillingDueDate datetime , 13> PaymentTotal INTEGER, 14> CreditTotal INTEGER 15> 16> ); 17> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> drop table Billings; 4> GO 1></source>


Performing Date Conversions

   <source lang="sql">

3> 4> SELECT CONVERT(varchar(20), GETDATE(), 101) 5> GO

10/19/2007 (1 rows affected)</source>


SELECT CONVERT(datetime, CONVERT( varchar(11), "2005-37:22.570", 101))

   <source lang="sql">

3> 4> SELECT CONVERT(datetime, CONVERT( varchar(11), "2005-08-13 20:37:22.570", 101)) 5> GO

2005-08-13 00:00:00.000 (1 rows affected)</source>


SET start_Date = GETDATE()

   <source lang="sql">

5> CREATE TABLE employee( 6> id INTEGER NOT NULL PRIMARY KEY, 7> first_name VARCHAR(10), 8> last_name VARCHAR(10), 9> salary DECIMAL(10,2), 10> start_Date DATETIME, 11> region VARCHAR(10), 12> city VARCHAR(20), 13> managerid INTEGER 14> ); 15> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid

---------- ---------- ------------ ----------------------- ---------- -------------------- ----------- 
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10

(9 rows affected) 1> 2> 3> UPDATE Employee 4> SET start_Date = GETDATE() 5> 6> 7> drop table employee; 8> GO (9 rows affected) 1> 2></source>


Specify date with string (OrderDate = "7/4/1996")

   <source lang="sql">

4> CREATE TABLE Orders ( 5> OrderID int 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> CREATE TABLE OrderDetails ( 4> OrderID int NOT NULL , 5> ProductID int NOT NULL , 6> UnitPrice money NOT NULL DEFAULT (0), 7> Quantity smallint NOT NULL DEFAULT (1), 8> Discount real NOT NULL DEFAULT (0) 9> ) 10> GO 1> INSERT OrderDetails VALUES(10248,11,14,12,0) 2> INSERT OrderDetails VALUES(10248,42,9.8,10,0) 3> INSERT OrderDetails VALUES(10248,72,34.8,5,0) 4> INSERT OrderDetails VALUES(10249,14,18.6,9,0) 5> INSERT OrderDetails VALUES(10249,51,42.4,40,0) 6> INSERT OrderDetails VALUES(10250,41,7.7,10,0) 7> INSERT OrderDetails VALUES(10250,51,42.4,35,0.15) 8> INSERT OrderDetails VALUES(10250,65,16.8,15,0.15) 9> INSERT OrderDetails VALUES(10251,22,16.8,6,0.05) 10> INSERT OrderDetails VALUES(10251,57,15.6,15,0.05) 11> 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 rows affected) (1 rows affected) 1> 2> 3> SELECT DISTINCT o.OrderDate, od.ProductID 4> FROM Orders o 5> JOIN OrderDetails od 6> ON o.OrderID = od.OrderID 7> WHERE OrderDate = "7/4/1996" 8> GO OrderDate ProductID

-----------

(0 rows affected) 1> 2> drop table orders; 3> drop table OrderDetails; 4> GO</source>


Store the date and time for a change in a datetime format.

   <source lang="sql">

The CURRENT_TIMESTAMP function returns this value. 4> 5> CREATE TABLE T ( 6> int1 int, 7> bit1 bit NOT NULL DEFAULT 0, 8> rvr1 timestamp, 9> usr1 nvarchar(128) DEFAULT USER, 10> createtime datetime DEFAULT CURRENT_TIMESTAMP 11> ) 12> GO 1> 2> drop table t; 3></source>


Use "LIKE pattern" match with a date type value

   <source lang="sql">

8> CREATE TABLE titles( 9> title_id varchar(20), 10> title varchar(80) NOT NULL, 11> type char(12) NOT NULL, 12> pub_id char(4) NULL, 13> price money NULL, 14> advance money NULL, 15> royalty int NULL, 16> ytd_sales int NULL, 17> notes varchar(200) NULL, 18> pubdate datetime NOT NULL 19> ) 20> 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 pubdate LIKE "Jul 8 1996%" 4> GO title_id title type pub_id price advance royalty ytd_sales notes 

                                                                                                                                                                                              pubdate
-------------------------------------------------------------------------------- ------------ ------ --------------------- --------------------- ----------- ----------- ----------
---------

(0 rows affected) 1> drop table titles; 2> GO 1> 2></source>


Using comperison operator with Date value

   <source lang="sql">

6> CREATE TABLE employee( 7> id INTEGER NOT NULL PRIMARY KEY, 8> first_name VARCHAR(10), 9> last_name VARCHAR(10), 10> salary DECIMAL(10,2), 11> start_Date DATETIME, 12> region VARCHAR(10), 13> city VARCHAR(20), 14> managerid INTEGER 15> ); 16> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid

---------- ---------- ------------ ----------------------- ---------- -------------------- ----------- 
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10

(9 rows affected) 1> 2> SELECT First_Name 3> FROM Employee 4> WHERE start_Date >= "1-1-2001" AND start_Date <= "12-31-1985"; 5> GO First_Name

(0 rows affected) 1> 2> 3> 4> drop table employee; 5> GO 1> 2> 3></source>

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