SQL Date column issues in query

  • declare @startdate datetime

    set @startdate = '01-01-2016'

    declare @enddate datetime

    set @startdate = '10-10-2016'

    IF(@startdate is NULL or @enddate is NULL)

    BEGIN

    select * from TableA A

    inner join TableB B

    on A.col1=B.col2

    WHERE

    A.col2='xxx'

    and

    (( A.col3 in ('AAA') or A.col3= 'NULL')

    OR

    (A.col4 in ('BBB') or A.col4= 'NULL'))

    END

    ELSE

    BEGIN

    select * from TableA A

    inner join TableB B

    on A.col1=B.col2

    WHERE

    A.col2='xxx'

    and

    (( A.col3 in ('AAA') or A.col3= 'NULL')

    OR

    (A.col4 in ('BBB') or A.col4= 'NULL'))

    AND

    (A.Dt between @startdate and @enddate))

    END

    When I plug in start and enddates it is not working properly.It is returning data irrepective of the dates supplied.

    A.Dt is a datetime column.

    Any suggestions please.

  • Try running with the variable dates in dd-mon=ccyy format to help with debugging, ie '01 jan 1900

  • You SET @startdate twice instead of setting @enddate 🙂

    ..

    set @startdate = '01-01-2016'

    declare @enddate datetime

    set @startdate = '10-10-2016' --should be set @enddate = ...

    ...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • You're setting your @startdate twice, so the @enddate is NULL.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • dogramone (5/26/2016)


    Try running with the variable dates in dd-mon=ccyy format to help with debugging, ie '01 jan 1900

    Do not EVER do this. I believe that there are only two formats that are guaranteed to be locale independent: 'YYYYMMDD hh:mm:ss' and 'YYYY-MM-DDThh:mm:ss' (note the "T" between the date portion and the time portion).

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (5/26/2016)


    dogramone (5/26/2016)


    Try running with the variable dates in dd-mon=ccyy format to help with debugging, ie '01 jan 1900

    Do not EVER do this. I believe that there are only two formats that are guaranteed to be locale independent: 'YYYYMMDD hh:mm:ss' and 'YYYY-MM-DDThh:mm:ss' (note the "T" between the date portion and the time portion).

    Drew

    +100000

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks Luiz and Drew for puting me straight, much appreciated.

  • Piling on, don't use implicit convertion

    😎

    DECLARE @startdate DATETIME = CONVERT(DATETIME,'20160101',112);

  • oops...thanks ... feel stupid now.Thanks anyway !

  • The above code is part of an SSRS report.After setting variables properly it is still not filtering data based of the date column.

  • When creating SSRS reports with date ranges I found the technique described in http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/133147/ works very well, particularly from a user perspective.

    ...

  • You should ideally create two Parameters in your SSRS Report for the dates. If the dates are semi-static or are 'relative' dates (today, yesterday, a fortnight ago), use an Expression in the Parameter's Default Properties to set the dates; otherwise the user can choose dates at run time. For 'choosable' dates, set a Default value of NULL; for 'fixed' dates, make the Parameter Internal so that the user isn't prompted for it.

    Suppose you name your parms. @From and @To, then at the top of your Database query you would write:

    DECLARE @startdate AS DATETIME

    DECLARE @enddate as DATETIME

    SET @startdate = @From

    SET @enddate = @To

    This eliminates all date constant shenanigans (though when required, as others have said, RIGOROUSLY use the YYYY-MM-DDThh:mm:ss format to avoid any problems).

    PS: Yes, HappyGeek is right about John Tamburo's 'dynamic date' concept and I've used an adapted version of that myself with great success in some reports. It's not a universal panacea though, and more savvy users who know the exact dates they want will often — in my experience — prefer using a plain old standard calendar picker.

  • sqlnewbie17 (5/26/2016)


    The above code is part of an SSRS report.After setting variables properly it is still not filtering data based of the date column.

    The above query cannot be a part of any SSRS report.

    It cannot be parsed.

    The number of closing brackets in it exceeds the number of opening ones by 1.

    Please past the actual code from your SSRS report.

    _____________
    Code for TallyGenerator

Viewing 13 posts - 1 through 12 (of 12 total)

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