Posted by Erland Sommarskog on 04/09/07 22:07

Dinesh (dinesht15@gmail.com) writes:
> I am working on SSRS 2005, and I am facing a problem in counting the
> no of days.
> My database has many fields but here I am using only two fields
> They are Placement_Date and Discharge_Date
> If child is not descharged then Discharge_Date field is empty.
>
> I am writing below query to count the number of days but is is not
> working it is showing the error
> "The conversion of a char data type to a datetime data type resulted
> in an out-of-range datetime value."
>
> select case
> when convert(datetime,Discharge_Date,103) = '' then

This does not really make sense. A datetime value cannot be the
empty string. By the default the empty string will convert to the
datetime value 1900-01-01 00:00:00.000, but I don't think that is
what you want.

Assuming a reasonably designed database, the test would be

WHEN Discharge_Date IS NULL THEN

Then again, since you seem to store dates in character values, this
may not be a reasonably designed database. :-)

Anyway, the problem appears to be that you have junk in your character
columns. As Plamen said, you should use the datetime data type to store
your dates instead.


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