Post reply

dates out-of-range value

  • August 17, 2015 at 3:47 am

    #303483

    Hi All,

    I was doing so well with SSRS but this time dates going to break me.

    I have table with dates in following format 2015-01-01 23:58:00.000 and my SQL SELECT query has following clause

    WHERE (CONVERT(VARCHAR(10), cast( arrivaldate as datetime), 103)) between @StartDate and @EndDate

    I can execute it in SSRS query designer with manually entering date parameters in "define query parametrs" e.g. 01/01/2015 it works fine but when I run report ....The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    any advise appreciated.

  • August 17, 2015 at 4:50 am

    #1820380

    Please will you post your whole query and a CREATE TABLE statement for the table you're selecting from?

    Thanks

    John

  • August 17, 2015 at 6:08 am

    #1820400

    don't convert dates to strings! the where statement you posted ends up causing a table scan, as it converts every date to a string, which in turn gets compared to a date.

    dates should be dates, and stay dates.

    WHERE (CONVERT(VARCHAR(10), cast( arrivaldate as datetime), 103)) between @StartDate and @EndDate

    assuming StartDate and EndDate are datetime parameters, you can simply do this:

    WHERE arrivaldate between @StartDate and @EndDate

    if arrival date is not a datetime(which is implied by the double conversion)

    you might need a case statement or an extra comparison in the WHERE clause

    WHERE ISDATE(arrivaldate ) = 1 AND CONVERT(datetime,arrivaldate) between @StartDate and @EndDate

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 17, 2015 at 7:28 am

    #1820427

    thanks for reply guys. total fail on my side ...not sure why I got in to my head that I need to convert this to dd/mm/yyyy format before comparing it to parameter . again thx for pointers

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply