Problem using CAST with Zeroed out Field Values

Hello --
I have a field with date values like: 20050714. When using SQL Server
to convert it to a Date in a View, I use the following:
CAST(MyDate as DateTime)

That works great. The problem is that some rows have the value:
00000000

When it hits one of these, I get a SQL Server Enterprise Manager Error:

Database Server: Microsoft SQL Server
Version: 08.00.0760
Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.

Is there a workaround for this? Any assistance greatly appreciated.

RBollinger1) Quit storing dates as strings and numbers. This is the REAL answer.
And kill the guy who did this so he will not screw up the rest of the
database.

2) You did not give us specs; did you want the non-existent date
0000-00-00 to be a NULL or what? The error is exactly right ad shows
that your design has some serious problems. .

CAST (CASE @.my_string_date
WHEN '00000000'
THEN NULL -- or dummy value
ELSE @.my_string_date END AS DATETIME)

A better would be to do an update on the column, get rid of the
mythical dates and then replace that column with a temporal one after
validating the existing dates with a procedure. Don't just mop the
floor; fix the leak!|||robboll (robboll@.hotmail.com) writes:
> I have a field with date values like: 20050714. When using SQL Server
> to convert it to a Date in a View, I use the following:
> CAST(MyDate as DateTime)
> That works great. The problem is that some rows have the value:
> 00000000
> When it hits one of these, I get a SQL Server Enterprise Manager Error:
> Database Server: Microsoft SQL Server
> Version: 08.00.0760
> Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
> conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> Is there a workaround for this? Any assistance greatly appreciated.

You can use the isdate() function to check whether a string is a
legal date value or not.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Combining your answer with the second guys answer, I think I will try
something like:

CAST(CASE@.my_string_date
WHEN isdate(my_string_date) = True
THEN NULL
ELSE @.my_string_date END AS DATETIME)

Thanks for your help!
RBollinger|||"isdate(my_string_date) = TRUE " is wrong; there is no Boolean datatype
or constants in SQL. You need a numeric value Did you fix the leak?|||--CELKO-- wrote:
> "isdate(my_string_date) = TRUE " is wrong; there is no Boolean
datatype
> or constants in SQL. You need a numeric value Did you fix the leak?

Yes I did "fix the leak". I ended up removing all invalid date values
as part of the append process -- the recommended solution.

Thanks.