Hi Dwain,
My query seemed to work on some server which runs SQL server 2000. When I execute the same on SQL Server 2008, I get the error: Conversion failed when converting date and/or time from character string.
My table is like this:
CRM_Request
(
Id [int] <PK, identity>,
TRNumber [nvarchar](50),
.....,
ExpectedCompletionDate [nvarchar](50),
UploadDate [nvarchar](50)
)
My actual query is this:
/*@DateSpec = 1 ---> fetch [ExpectedCompletionDate]; 2 ---> fetch [UploadDate] */
SELECT TRRpt.Id TRId, TRRpt.TRNumber, DateSpecified
FROM
(
select a.Id, a.TRNumber,
(
CASE WHEN @DateSpc = 1 THEN a.ExpectedCompletionDate
ELSE LTRIM(RTRIM(a.UploadDate))END
) AS DateSpecified,
from CRM_Request a
where
(
(@DateSpc = 1 and IsDate(a.ExpectedCompletionDate) = 1)
or
(@DateSpc = 2 and IsDate(a.UploadDate) = 1)
)
) TRRpt
WHERE
DateDiff(d, TRRpt.DateSpecified, @StartDate) <= 0 and
DateDiff(d, TRRpt.DateSpecified, @EndDate) >= 0
If I remove the "where clause" the query throws results with the dates like
8/31/2011 5:27:44 PM
09/26/2011
08/28/2012
8/7/2007
06-09-2009
08/13/10
08/18/10
9/9/2010
As you can see, it has all possible datetime formats for strings.
Can you plz tell me what seems to be the problem?
Thanks in advance,
Venkat R Prasad.