If you want to validate text-strings in a table field, you can use a TALLY table to match the values against.

Create a table with a datetime field and fill it with all dates within the required range. Join this tally table with the table that holds the text-string values. Convert the datetime value of the tallytable to seperate values for year, month and day (P.S.: these values could also be added as extra columns in the tally-table). Match these year, month and day values to substrings of the text value.

create table tally_date (id int identity (1,1)

, date_value date)

declare @int int

set @int = 0

while @int < 366

begin

insert into tally_date

select dateadd(day, @int, '20120101')

set @int = @int + 1

end

select * from tally_date

create table validate_dates (date_value nvarchar(10))

insert into validate_dates

select '2012-02-00'

union allselect '2012-02-10'

union allselect '2012-02-29'

union allselect '2012-03-10'

union allselect '2012-03-21'

union allselect '2012-21-04'

-- join the textstring values with the tally-table and list all mismatches (i.e. invalid dates) first

select

vd.date_value

, left(vd.date_value, 4) as year_value

, substring(vd.date_value, 6, 2) as month_value

, right(vd.date_value, 2) as day_value

, td.date_value

from validate_dates vd

left outer join tally_date td

on left(vd.date_value, 4) = year(td.date_value)

and substring(vd.date_value, 6, 2) = month(td.date_value)

and right(vd.date_value, 2) = day(td.date_value)

order by

td.date_value