Function F_TABLE_DATE is a multistatement table-valued function that returns a table containing a variety of attributes of all dates from @FIRST_DATE through @LAST_DATE. In short, it’s a calendar table function.

This just kept growing as I adding more and more items. I think this is finished enough to share, since it is up to around 65 columns.

The general operation of the function and the contents of the output columns are documented in the code. If you any questions, post them here, and I'll respond.

If you spot any problems or bugs or have any suggestions, I would appreciate any feedback.

Edit:Modified 2006-07-07 to support systems running a non-US English DATEFORMAT. Also added a checksum test to the script to confirm that results are the same for different date formats. This change does not convert the various output formats to local formats; it only makes the function produce the same output no matter what the DATEFORMAT is.

If you have the earlier version, you may want to get this updated code.

Edit (2012-07-05):Corrected error in contents of column DATE_FORMAT_YYYY_M_D. It was returning the date in format YYYY/D/M, instead of YYYY/M/D. Error was reported by MorrisK in post on this thread on 02/08/2012 15:40:08, but I just noticed his post today.

"-- Return a pesudo error message by trying to-- convert an error message string to an int.-- This method is used because the error displays-- the string it was trying to convert, and so the-- calling application sees a formatted error message"

I like the sound of that ... I've been looking for a waysome code to steal to "return" an error from a Function.

one remark...if either of the input dates exceeds the lower/upper limit this is returned:Server: Msg 242, Level 16, State 3, Line 2The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

so i guess that you don't even have to handle those errors in the function, no?

one remark...if either of the input dates exceeds the lower/upper limit this is returned:Server: Msg 242, Level 16, State 3, Line 2The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

so i guess that you don't even have to handle those errors in the function, no?

Go with the flow & have fun! Else fight the flow

Actually, there are valid datetime values that the function cannot process. It rejects dates before 1754-01-01 and after 9997-12-31, because I didn't want to spend a lot of time programming around edge contitions for ISO Weeks. I figured that not being able to load years 1753, 9998, and 9999 would not be a huge drawback for most applications.

quote:Originally posted by rockmoose...We will implement a dw pretty soon, or at least improve on the existing, I sense that the function will save me some coding.Will store the values in a table though...

A lot of the work I did on this was for a data warehouse that I did years ago. I saw lots of examples that had a few of these ideas, but I wanted a Swiss Army Knife date dimension, the one with 60 blades, so I developed this.

As I said in the comments in the code, this function was meant to load a table. However, if you need it to load a small temporary table, or to use it directly, it works just fine, and is fairly fast for small result sets. You can generate a result set with as little as one row.

Once you load a table, I found it was very useful to create a view that selects the row for the current day. It saves a lot of work in coding to be able to get the data for the current day directly.

Msg 207, Level 16, State 1, Procedure F_TABLE_DATE, Line 502Invalid column name 'ISO_WEEK_YEAR'.Msg 15151, Level 16, State 1, Line 2Cannot find the object 'F_TABLE_DATE', because it does not exist or you do not have permission.Msg 208, Level 16, State 1, Line 3Invalid object name 'dbo.F_TABLE_DATE'.

Msg 207, Level 16, State 1, Procedure F_TABLE_DATE, Line 502Invalid column name 'ISO_WEEK_YEAR'.Msg 15151, Level 16, State 1, Line 2Cannot find the object 'F_TABLE_DATE', because it does not exist or you do not have permission.Msg 208, Level 16, State 1, Line 3Invalid object name 'dbo.F_TABLE_DATE'.

any ideas?

I just copied the script from the posted code, and it ran fine from SQL 2000 Query Analyzer.