Creating TimeSlots with SQL DateTime Column

On this quick post, I want to memo how I used a SQL Temporary Table to build a temporary data store of half hour time slots within a 24 hour day on either a selected date or current date.

First I create a temporary table, and then I use two DateTime variables (Timeslots & Counter). Then using the DateAdd() SQL Function, I set the variables to midnight 00:00 until 00:00 next day for a given date or current date.

For example, 21 Dec 2012 00:00 am to 22 Dec 2012 00:00 am to get a perfect 24 hours

CREATE TABLE #Times(HalfHourGaps DateTime)
DECLARE @TimeSlots DateTime
DECLARE @Counter DateTime

SET @TimeSlots = getdate()
SET @Counter = getdate()

SET @TimeSlots = dateadd(dd, 0, datediff(dd, 0, @TimeSlots))
SET @Counter = dateadd(dd, 1, datediff(dd, 0, @Counter))

WHILE (@TimeSlots <= @Counter)
INSERT INTO #Times(@TimeSlots)
SET @TimeSlots = dateadd(minute, 30, @TimeSlots)

SELECT HalfHourGaps FROM #Times

Now, when I obtained 30 minutes timeslots in temporary table, I can now check against any existing DateTime column in SQL table to see if datetime column value exists or matches in this temporary table.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s