How to check whether (var)char is a valid ISO date (112)

I have a char(8) field which should really contain a value in a yyyymmdd dateformat. Given a (hypothetical) table开发者_开发问答 id(int)|datestring(char(8)) I would like to be able to do something like

SELECT id, isValidDate(datestring) FROM my_hypothetical_table

It's important for me that this can be run like so as a query (so I could, for example, SELECT * from othertable INNER JOIN hypothetical_table on hypothetical_table.id = othertable.hypothetical_FK WHERE isValidDate(hypothetical_table.datestring) = 1). Catching errors doesn't seem viable.

Note that the IsDate() function only works with slash delimited dates, and not yyyymmdd formats.

You may want to add a check for length if you are unsure about all values being 8 digits in length.

DECLARE @MyTable TABLE (id INT, datestring CHAR(8))
INSERT INTO @MyTable VALUES (1, '20110711')
INSERT INTO @MyTable VALUES (2, '2011')
INSERT INTO @MyTable VALUES (3, '20110228')
INSERT INTO @MyTable VALUES (4, '20110229')
INSERT INTO @MyTable VALUES (5, '2011071')
INSERT INTO @MyTable VALUES (6, '201107')
SELECT id, datestring, ISDATE(datestring) AS IsDate FROM @MyTable

Running this produces the output:

id          datestring IsDate
----------- ---------- -----------
1           20110711   1
2           2011       1
3           20110228   1
4           20110229   0
5           2011071    0
6           201107     1

ISDATE() will work with YYYYMMDD format too

SELECT ISDATE('20010101')