469,936 Members | 2,365 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,936 developers. It's quick & easy.

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.

RBollinger

Jul 23 '05 #1
5 1240
1) 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!

Jul 23 '05 #2
robboll (ro*****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
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

Jul 23 '05 #4
"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?

Jul 23 '05 #5

--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.

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Zoury | last post: by
17 posts views Thread by Jon Slaughter | last post: by
10 posts views Thread by babak | last post: by
5 posts views Thread by junglist | last post: by
2 posts views Thread by ajikoe | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.