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

Question

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.

Why on earth do you think ISDATE() only works with slash-delimited dates??? And why on earth are you storing a date in a CHAR(8) column? — Aaron Bertrand, Jul 11 '11 at 13:59
yyyymmdd is the only safe date format for SQL Server. Read the comments to the 2 answers: http://stackoverflow.com/q/1138142/27535 — gbn, Jul 11 '11 at 14:05
I mistakenly thought so because all the exmples listed on http://msdn.microsoft.com/en-us/library/ms187347(v=SQL.100).aspx use the format. Showing how to order your m/d/y — Martijn, Jul 11 '11 at 15:21

score 5 · Answer 1 · answered Jul 11 '11 at 13:51

5

ISDATE() will work with YYYYMMDD format too

SELECT ISDATE('20010101')

answered Jul 11 '11 at 13:51

Madhivanan

13,470
1
24
29

score 5 · Accepted Answer · answered Jul 11 '11 at 13:53

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

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

2 Answers2