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

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
> datediff(day,CONVERT(datetime,Placement_Date,103),GETDATE())
> else
> datediff(day,CONVERT(datetime,Placement_Date,
> 103),CONVERT(datetime,Discharge_Date,103))
> end NoOfDays
> from Placement_Details
> So please tell me where I am wrong?

Oh, by the way, this SELECT should give you the rows with bad dates:

SET DATEFORMAT DMY
go
SELECT Discharge_Date, Placement_Date
FROM Placement_Details
WHERE isdate(Discharge_Date) = 0 OR
isdate(Placement_Date) = 0


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