|
|
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
Navigation:
[Reply to this message]
|