In this short article I will explain how to
1. Use the SQL Server 2008 Time Data Type and define its columns in Tables.
2. Understanding the precision in Time Data Type.
3. Get Current System Time.
4. How to insert Time Values using SQL Query into Tables.
In order to explain the concept practically I have created a Table named as Meetings which is used to reserve Meeting Room by various employees in the company
Table with Time Data Type Columns
Below is the SQL Script to create a table named as Meetings.
CREATE TABLE [dbo].[Meetings](
[MeetingId] [int] IDENTITY(1,1) NOT NULL,
[ReservedBy] [varchar](15) NOT NULL,
[StartTime] [time](7) NOT NULL,
[EndTime] [time](7) NOT NULL,
CONSTRAINT [PK_Meetings] PRIMARY KEY CLUSTERED
(
[MeetingId] ASC
)
) ON [PRIMARY]
GO
![Understanding the SQL Server 2008 Time Data Type, it’s Precision, get Current Time and Compare Time Values]()
The Precision in Time Data Type
Now you will notice the number 7 next to the time data type [time](7). It is used for setting the precision value. If not specified the default value is 7.
SELECT 1, CAST(CONVERT(TIME(0),GETDATE()) AS VARCHAR(15))
UNION ALL
SELECT 2, CAST(CONVERT(TIME(1),GETDATE()) AS VARCHAR(15))
UNION ALL
SELECT 3, CAST(CONVERT(TIME(2),GETDATE()) AS VARCHAR(15))
UNION ALL
SELECT 4, CAST(CONVERT(TIME(3),GETDATE()) AS VARCHAR(15))
UNION ALL
SELECT 5, CAST(CONVERT(TIME(4),GETDATE()) AS VARCHAR(15))
UNION ALL
SELECT 6, CAST(CONVERT(TIME(5),GETDATE()) AS VARCHAR(15))
UNION ALL
SELECT 7, CAST(CONVERT(TIME(6),GETDATE()) AS VARCHAR(15))
UNION ALL
SELECT 8, CAST(CONVERT(TIME(7),GETDATE()) AS VARCHAR(15))
UNION ALL
SELECT 9, CAST(CONVERT(TIME,GETDATE()) AS VARCHAR(15))
![Understanding the SQL Server 2008 Time Data Type, it’s Precision, get Current Time and Compare Time Values]()
Get Current System Time
The following query can be used to get the Current System Time
SELECT CONVERT(TIME(0),GETDATE())
Inserting Time Values in Table using SQL Query
Using the below query I have inserted some rows in the Meetings table
INSERT INTO Meetings
VALUES
('John', '12:00:00', '13:00:00'),
('Robert', '14:00:00', '14:30:00'),
('Maria', '15:00:00', '16:00:00')
After insert the table has following records.
![Understanding the SQL Server 2008 Time Data Type, it’s Precision, get Current Time and Compare Time Values]()
Comparing the Time Values
Now we will learn how we can compare the Time Values in SQL Queries.
The situation is such that two people Adam and Sheryl want to reserve the meeting room as per the following schedule
Adam - 14:15:00 - 14:35:00
Sheryl - 14:35:00 - 14:45:00
Now will execute INSERT Queries for both of them one by one. The below query checks whether the time period requested must not be occupied or reserved by some other person.
Adam
DECLARE @Name NVARCHAR(15),
@Start [time],
@End [time]
SET @Name = 'Adam'
SET @Start = '14:15:00'
SET @End = '14:35:00'
IF NOT EXISTS(SELECT * FROM Meetings
WHERE (@Start BETWEEN [StartTime] AND [EndTime])
OR(@End BETWEEN [StartTime] AND [EndTime]))
BEGIN
INSERT INTO Meetings
VALUES (@Name, @Start, @End)
END
In Adam’s case the meeting room is already reserved from 14:00:00 – 14:30:00 hence his requested time 14:15:00 – 14:35:00 cannot be allotted and hence no record is inserted.
Sheryl
DECLARE @Name NVARCHAR(15),
@Start [time],
@End [time]
SET @Name = 'Sheryl'
SET @Start = '14:35:00'
SET @End = '14:45:00'
IF NOT EXISTS(SELECT * FROM Meetings
WHERE (@Start BETWEEN [StartTime] AND [EndTime])
OR(@End BETWEEN [StartTime] AND [EndTime]))
BEGIN
INSERT INTO Meetings
VALUES (@Name, @Start, @End)
END
In Sheryl’s case the meeting room is vacant between 14:30:00 – 15:00:00 hence her requested time 14:35:00 – 14:45:00 is allotted and the record is inserted.
The table now has the following records.
![Understanding the SQL Server 2008 Time Data Type, it’s Precision, get Current Time and Compare Time Values]()
