How do I determine if text can be converted to Date?
While converting and normalizing data from an Access database to a SQL Server database, I ran across an interesting issue: ISDATE() does not appear to successfully predict whether text can be cast to a date.
  
    SELECT 'This will return an error' =
    	CASE
    	WHEN ISDATE('1/1-2010') = 1 THEN CAST('1/1-2010' AS date)
    	ELSE NULL
    	END

I thought this was a bit odd, as the [ISDATE() MSDN article](http://msdn.microsoft.com/en-us/library/ms187347.aspx) states that the function will return "1 if the expression is a valid date, time, or datetime value; otherwise, 0."

From what I could tell, this error is not mitigated by playing with either SET DATEFORMAT or SET LANGUAGE.

Nor does CONVERT() appear to have a different result than CAST().

Fortunately, ISDATE() does successfully predict whether text can be converted to a *datetime*.

    SELECT 'This will return a datetime' =
    	CASE
    	WHEN ISDATE('1/1-2010') = 1 THEN CAST('1/1-2010' AS datetime)
    	ELSE NULL
    	END

So, I *could* use a workaround of casting to a datetime and then casting to a date.

    SELECT 'This will return a date' =
    	CASE
    	WHEN ISDATE('1/1-2010') = 1 THEN CAST(CAST('1/1-2010' AS datetime) as date)
    	ELSE NULL
    	END

However, I wonder if I am missing something. Is there a cleaner way to cast text to a date? As a corollary, is the ISDATE() function not defined correctly on MSDN (and actually therefore a bit of a misnomer)?

How do I determine if text can be converted to Date?
===

While converting and normalizing data from an Access database to a SQL Server database, I ran across an interesting issue: ISDATE() does not appear to successfully predict whether text can be cast to a date.
  
    SELECT 'This will return an error' =
    	CASE
    	WHEN ISDATE('1/1-2010') = 1 THEN CAST('1/1-2010' AS date)
    	ELSE NULL
    	END

I thought this was a bit odd, as the [ISDATE() MSDN article](http://msdn.microsoft.com/en-us/library/ms187347.aspx) states that the function will return "1 if the expression is a valid date, time, or datetime value; otherwise, 0."

From what I could tell, this error is not mitigated by playing with either SET DATEFORMAT or SET LANGUAGE.

Nor does CONVERT() appear to have a different result than CAST().

Fortunately, ISDATE() does successfully predict whether text can be converted to a *datetime*.

    SELECT 'This will return a datetime' =
    	CASE
    	WHEN ISDATE('1/1-2010') = 1 THEN CAST('1/1-2010' AS datetime)
    	ELSE NULL
    	END

So, I *could* use a workaround of casting to a datetime and then casting to a date.

    SELECT 'This will return a date' =
    	CASE
    	WHEN ISDATE('1/1-2010') = 1 THEN CAST(CAST('1/1-2010' AS datetime) as date)
    	ELSE NULL
    	END

However, I wonder if I am missing something. Is there a cleaner way to cast text to a date? As a corollary, is the ISDATE() function not defined correctly on MSDN (and actually therefore a bit of a misnomer)?
