sql: Cannot convert to Date

Question

I have the following sql:

     select CONVERT(date,visitorannouncements.DateOfVisit), visitorannouncements.Agent,
            Firstname, visitors.Lastname 
       from Visitors

       LEFT JOIN visitorannouncements 
            ON Visitors.VisitorAnnouncementId=visitorannouncements.Id
      where ship = @shipname 
        AND convert(datetime, visitorannouncements.DateOfVisit,101) BETWEEN  @startDate AND @endDate

The visitorannouncements.DateOfVisit is nvarchar field.

I get the following error:

TITLE: Microsoft SQL Server Report Builder

An error occurred while reading data from the query result set. The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

ADDITIONAL INFORMATION:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. (Microsoft SQL Server, Error: 242)

Is this the correct query? You have from Visitors twice there! Please format your query correctly, that helps a lot! — Ocaso Protal
Just as an aside you'd be better off parsing the DateOfVisit at the source (WebApp/Rich Client) so when it gets to your database it's a true DateTime type. That or have a process that cleans up data before you send it to SSRS. — booyaa

muhmud muhmud · Accepted Answer · 2013-02-27T09:00:14

Try finding out which visitorannouncements.DateOfVisit is not a valid date:

select *
from visitorannouncements
where isdate(DateOfVisit) = 0

sql: Cannot convert to Date

2 Answers