Posted by Erland Sommarskog on 04/01/07 15:20

pinney.colton@gmail.com (pinney.colton@gmail.com) writes:
> I see the approach you're taking. I should have been more clear in my
> original post - it's not just bool's that I have issues with - it's
> every data type. Data that should be stored as an int (or tinyint) is
> stored as a varchar, data that should be stored as a datetime is
> stored as a varchar, data that should be stored as a float is stored
> as a varchar. I'm looking to convert all of that data. Bool data was
> just an example.

The exact validation is different depending on data type. Important is
the use of the CASE expression:

SELECT CASE isdate(varcharcol) THEN convert(datetime, varcharcol) END
FROM tbl
WHERE isdate(varcharcol) = 1

If you don't have the CASE, you can still get a conversion error despite
the filter in the WHERE clause.

Dates are particularly nasty to deal with. If a column says 01/02/03,
what this mean? Feb 1st, 2003, 2nd Jan 2003, March 2nd 2001 or something
else?



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]