SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Tally Table - Character Date Validation

Tally (or numbers) tables are one of my favorite query writing tools.  Such a simple premise that can be applied to a wide variety of problems.   If you don't know what they are, I'd recommend this article.   Here is one problem I've seen a few times now that you can use a Tally table to easily solve.  This is by no means the only way to do something like this, nor is it necessarily even the *best* way, but it is *a* way.

The Problem:

You have a char/varchar column that holds dates in YYYYMMDD format.  You now need to cast these values to dates.  The problem is, with no validation on the column, some of these values aren't actually valid dates and you get conversion errors.  You'd like to go through and identify anything that didn't follow the pattern of YYYYMMDD or wasn't a valid date so that it can be either fixed or removed. 

The Tally Solution:

The following query uses a Tally table to generate a range of valid dates that you can check your varchar field against.  The below version works in 2000+.  (You can just as easily use a CTE tally in 2005/2008)

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '19910101 00:00:00'
SET @EndDate = '20250101 00:00:00'

FROM YourTable
WHERE vcdatecolumn NOT IN (
select CAST(YEAR(dateadd(d,n,@StartDate)) as char(4)) +
RIGHT('0' + CAST(MONTH(dateadd(d,n,@StartDate)) as varchar(2)),2) +
RIGHT('0' + CAST(DAY(dateadd(d,n,@StartDate)) as varchar(2)),2)
from Tally
WHERE DATEADD(d,n,@StartDate) <= @EndDate

Other Methods:
As mentioned, there are a lot of ways to do this.  Because ISDATE() jumps out as being such an easy one, I'll mention a couple of the problems with using that method.  The main gap left is that some of the junk values (fairly common with this setup) like '9901' are valid dates according to ISDATE().  However, it is unlikely that this date is really supposed to be 9901/01/01.  You can add a length check, but then you run into '9901June' still being valid, etc. etc.  Using the Tally method, all of these are simply flagged as invalid.  You can make up your mind about what to do with them once they've been identified. 


Posted by crussell on 17 December 2009

Wow. Tally tables are great. In fact there should be a built in Tally table that just comes with SQL.


P.S. Though I am a Star Trek fan, I'm sure you meant @StartDate, not @StarDate in that last line.

Posted by Seth Phelabaum on 17 December 2009

Ack!  Indeed I did.  Thanks for the heads up.  Fixed the original.

Posted by James Goodwin on 17 December 2009

Is there any reason not to use:

WHERE vcDateColumn not in (SELECT CONVERT(varchar(8),dateadd(d,n,@StartDate),112) FROM Tally ...

Posted by Seth Phelabaum on 17 December 2009

Nope.  That works too.  I always have a hard time figuring out where to draw the line on talking about any given topic, and originally I was going to go into how the same technique could be used for things other than just dates.  As it kept going, everything I added led me to something else, and eventually I decided to try to keep the individual posts shorter and to the point (I'm planning to do several more tally examples).  I think the 'building' of the date string makes it a little more clear how it is actually working and has a better chance of making people realize it isn't limited to dates.  It's also easy to adapt if their format doesn't follow one of the built ins.

On a side note, I was curious and tested the two methods side by side.  There does not seem to be any performance difference.

Posted by RBarryYoung on 24 December 2009

Carl: couldn't agree more.  In fact if MS did it right, it would be an internal virtual Numbers/Tally table that could far outperform anything that we could write ourselves.

Posted by RBarryYoung on 24 December 2009

Great article, Seth.  Very useful.

By the way, I love the picture,  Makes you look all buffed out.  :-)

Posted by Seth Phelabaum on 14 January 2010

What do you mean 'look'!?!?!  I AM!  All thanks to my strict regiment of trying to hit the gym at least once a year.  ;)  Thanks for the feedback Barry.

Posted by mail2payan on 7 April 2010

This is a great blog..thanks!!

Leave a Comment

Please register or log in to leave a comment.