Between date : datetime « Data Types « SQL Server / T-SQL Tutorial

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>

5.9.datetime
	5.9.1.	datetime type
	5.9.2.	Store the date and time for a change in a datetime format.
	5.9.3.	Billings on or before a specified date
	5.9.4.	A BETWEEN phrase with literal values
	5.9.5.	Using comperison operator with Date value
	5.9.6.	Between date
	5.9.7.	SET start_Date = GETDATE()
	5.9.8.	Use 'LIKE pattern' match with a date type value
	5.9.9.	An UPDATE statement that assigns new values to datetime column with string value
	5.9.10.	CAST(OrderDate AS varchar)
	5.9.11.	Specify date with string (OrderDate = '7/4/1996')
	5.9.12.	Performing Date Conversions
	5.9.13.	SELECT CONVERT(datetime, CONVERT( varchar(11), '2005-08-13 20:37:22.570', 101))
	5.9.14.	Add integer and float number to a date value
	5.9.15.	A SELECT statement that ignores date values
	5.9.16.	A SELECT statement that uses the CONVERT function to remove time values
	5.9.17.	A SELECT statement that uses the CAST function to remove time values
	5.9.18.	A SELECT statement that searches for month, day, and year components