Building a Weekend Table - SQL Dates and Times Series

Hello and welcome (back) to the SQL Coach blog!

Tonight I am going to show you how to build a table populated with the dates of all weekend days within a certain date range.

In this project we will be covering:

• The SmallDateTime SQL Server data type
• How to create a Variable Temporary Table in SQL Server
• How to loop through a date range in SQL Server T-SQL
• How to use DatePart to determine if a date refers to a Saturday or Sunday

Open a new query window and copy and paste the following code into it...

-- Get rid of Row(s) Inserted tracking and messaging
SET NOCOUNT ON

-- Create a temporary variable table
DECLARE @WeekendDays TABLE
(
dDate SmallDateTime,
sType VarChar(3)
)

-- Define the date range we will be using
DECLARE @dDate SmallDateTime,
@dEndDate SmallDateTime

SET @dDate = '19900101'
SET @dEndDate = '20251231'

-- Populate the temporary table with dates
-- of all Saturdays and Sundays that fall
-- within the date range
WHILE @dDate < @dEndDate
BEGIN
If DATEPART(WEEKDAY, @dDate) = 1
INSERT INTO @WeekendDays Values(@dDate, 'SUN')

Else If DATEPART(WEEKDAY, @dDate) = 7
INSERT INTO @WeekendDays Values(@dDate, 'SAT')

SET @dDate = DATEADD(DAY, 1, @dDate)
END

This code will create and populate a temporary variable table holding the dates of all Saturdays and Sundays from January 1, 1990 through December 31, 2025.

For the remainder of this post I will refer to this code as the base code.

Now, let's have some fun and check out the data in this table! ;)

Suppose you want to know how many Saturdays are in December 2007.

You can find out this information by adding the following query below the base code and executing it (it being all code, the whole enchilada):

SELECT COUNT(*) AS NUM_WEEKEND_DAYS_IN_DEC_2007
FROM @WeekendDays
WHERE dDate BETWEEN '20071201' AND '20071231'

Excellent! Now we know December 2007 has 10 weekend days.

Now, let's see the dates of these weekend days.

You can find the dates by adding the following query below the base code and executing it:

SELECT dDate
FROM @WeekendDays
WHERE dDate BETWEEN '20071201' AND '20071231'

You should see 10 rows that begin with this data...

2007-12-01 00:00:00
2007-12-02 00:00:00

Now, let's get rid of the time data because we really do not need it here.

SELECT Convert(Char(8), dDate, 112)
FROM @WeekendDays
WHERE dDate BETWEEN '20071201' AND '20071231'

Now, the data will look like this...

20071201
20071202

Now, let's Americanize this format.


SELECT Cast(Month(dDate) as VarChar)
+ '/' + Cast(Day(dDate) as VarChar)
+ '/' + Cast(Year(dDate) as VarChar)
FROM @WeekendDays
WHERE dDate BETWEEN '20071201' AND '20071231'

As you see, the data now looks like this...

12/1/2007
12/2/2007


I hope you now have a much better understanding of how to work with Dates in Sql Server. And I imagine you have some ideas you just can't wait to try out!

I apologize for the lack of indenting in the code. I actually wrote the code using indenting to make it easier to follow but once I published this post the formatting was removed. Ah well, these things happen. I think the code is clear enough you can easily follow it.

Remember to check back soon for more great SQL information. And please help us to spread the word about our blog. Tell your friends. Tell your co-workers. Heck, tell your family and even your pets!

Thanks!