A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.

Creating a Date Table in SQL

How do you create a table in SQL in your database that contains the many details about a particular date? I have encountered tables like this at various places I have worked and found then to be convenient  ways to do many date operations that otherwise would require some intricate manipulation of SQL date functions. These tables have several uses such as making date manipulation available to people who may not be experts in SQL, and allowing for some date details that may vary from one company to another like determining the fiscal year and standardizing on the ways dates are formatted.

I thought creating such a table would be an interesting exercise. Using what was available to me at the time, MS SQL, I have created a few date related tables, a view to display them, and an example of a T-SQL query that was made easier with the creation of the date table.

In the following code sections can all be put in a single query and ran at one time. I am not going to walk through the process I took to creating the table as there was much back and forth, I’ll just describe what I created.

To ease the re-running of the query so that tweaks and changes can be made I look for the existence of the tables and indexes I create and drop them if there are there, thus avoiding “already exists” error messages as I make modifications and re-run the query. (Please forgive any formatting quirks in the source code, I am trying to find a good source code formatting plug-in)

Then I created and populated the table. The starting date I used is the smallest date supported by the SQL Server’s datetime datatype. Why 1753? It is because by this time most of the world switched from the Julian to the Gregorian calendar. To calculate date prior to this, you would need to do the conversions based on the country that is your frame of reference. I found some of the details why written in a succinct way on the nixioncorp team blog.

This is all fine and good, but there are other things that may be useful such as weekday and month names as well as the month abbreviations:

And now I create a view to tie it all together:

Put all the above in a single query file and it should run on MS SQL. Other flavors of SQL should also work with possibly minor modifications.


Now as a demonstration of a query made easier because of this table consider the scenario of getting paid every 2 weeks on a Friday. Which months in the next few years would you have 3 paychecks a month?

I have made the files available for download here.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">