Home Forums SQL Server 2008 T-SQL (SS2K8) Comparision of a datetime variable against a column of the type nvarchar RE: Comparision of a datetime variable against a column of the type nvarchar

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