Current Date issue in where clause

  • Thanks Lynn. Is there a good place to find information on this topic? I've seen the below done 500 different ways and usually have trouble deciphering what some of it means. Thanks.

    declare @ReportDate datetime; -- Think of this as the input parameter requesting a specified date

    declare @StartDate datetime,

    @EndDate datetime;

    -- Assume that the date entered may have time component that needs striping

    set @StartDate = DATEADD(dd, DATEDIFF(dd, 0, @ReportDate), 0);

    set @EndDate = DATEADD(dd, 1, @StartDate);

  • Jeremy... (2/22/2012)


    Thanks Lynn. Is there a good place to find information on this topic? I've seen the below done 500 different ways and usually have trouble deciphering what some of it means. Thanks.

    declare @ReportDate datetime; -- Think of this as the input parameter requesting a specified date

    declare @StartDate datetime,

    @EndDate datetime;

    -- Assume that the date entered may have time component that needs striping

    set @StartDate = DATEADD(dd, DATEDIFF(dd, 0, @ReportDate), 0);

    set @EndDate = DATEADD(dd, 1, @ReportDate);

    In 2008, I prefer this:

    DECLARE @ReportDate DATE --if coming as input parameter, why even have @startDate?

    DECLARE @EndDate DATE

    SET @EndDate = DATEADD(dd,1,@StartDate)

    This avoids the datediff/dateadd crap that we had to use in 2005. If it is simply a date without time, Use the DATE data type. Also, to strip the time off simply do this:

    CAST(myDateTime AS DATE)

    Jared
    CE - Microsoft

  • MysteryJimbo (2/22/2012)


    Lynn Pettis (2/22/2012)


    Jeremy... (2/22/2012)


    MysteryJimbo (2/22/2012)


    I dont mean to be pedantic but for accuracy I'd want to see this query as a between and in a stored procedure with default values so it can be reused for any day and cater for incorrect data (just in case)

    What is a between?

    It is in a stored procedure and a report has been created of it and put on the report server.

    When you say using default values, do you mean something like:

    DECLARE @todaydatetime

    SET @today = CONVERT(DATE,FA.receiveddate)=CONVERT(DATE,GETDATE())

    would that be correct? Then substitute @today in the where clause?

    I would not use between if I were to write this to return data for a specific date. I would use the follow snippet as a start:

    declare @ReportDate datetime; -- Think of this as the input parameter requesting a specified date

    declare @StartDate datetime,

    @EndDate datetime;

    -- Assume that the date entered may have time component that needs striping

    set @StartDate = DATEADD(dd, DATEDIFF(dd, 0, @ReportDate), 0);

    set @EndDate = DATEADD(dd, 1, @StartDate);

    SELECT

    --- columns to be returned here

    FROM

    --- table a (or tables)

    WHERE

    a.OrderedDate >= @StartDate and

    a.OrderedDate < @EndDate -- followed by other sfilter conditions if needed

    ;

    You "could" do it either way; since BETWEEN is inclusive you would have to use a datetime with .997 ms. So 23:59:59.997.

    And then you have to change this if you are using datetime2 in SQL Server 2008.

    I much prefer using the >= and < when having to select records for a single date, you don't have to worry about the time portion.

  • Hakuna Matata (2/22/2012)


    Try this :

    WHERE CONVERT(DATE,FM.Openeddate)=CONVERT(DATE,GETDATE())

    I don't recommend to use this

  • Wildcat (2/22/2012)


    Hakuna Matata (2/22/2012)


    Try this :

    WHERE CONVERT(DATE,FM.Openeddate)=CONVERT(DATE,GETDATE())

    I don't recommend to use this

    I agree with wildcat as it would cause a table scan. The convert function has to be applied to every row in the table to complete the comparison.

  • Lynn Pettis (2/22/2012)


    Wildcat (2/22/2012)


    Hakuna Matata (2/22/2012)


    Try this :

    WHERE CONVERT(DATE,FM.Openeddate)=CONVERT(DATE,GETDATE())

    I don't recommend to use this

    I agree with wildcat as it would cause a table scan. The convert function has to be applied to every row in the table to complete the comparison.

    so it works, just chews up more processes and takes the query longer to complete?

  • Lynn Pettis (2/22/2012)


    Wildcat (2/22/2012)


    Hakuna Matata (2/22/2012)


    Try this :

    WHERE CONVERT(DATE,FM.Openeddate)=CONVERT(DATE,GETDATE())

    I don't recommend to use this

    I agree with wildcat as it would cause a table scan. The convert function has to be applied to every row in the table to complete the comparison.

    I still prefer CAST over CONVERT for performance:

    SELECT DATEADD(ss,RAND(),GETDATE()) AS RandomDate

    INTO #TEMP

    FROM sys.columns s1

    CROSS JOIN sys.columns s2

    SET STATISTICS TIME ON

    SELECT CONVERT(DATE, RandomDate) AS something

    FROM #TEMP

    SELECT CAST(RandomDate AS DATE) AS something

    FROM #temp

    Jared
    CE - Microsoft

  • Jeremy... (2/22/2012)


    Lynn Pettis (2/22/2012)


    Wildcat (2/22/2012)


    Hakuna Matata (2/22/2012)


    Try this :

    WHERE CONVERT(DATE,FM.Openeddate)=CONVERT(DATE,GETDATE())

    I don't recommend to use this

    I agree with wildcat as it would cause a table scan. The convert function has to be applied to every row in the table to complete the comparison.

    so it works, just chews up more processes and takes the query longer to complete?

    It gives you a bad execution plan. Much better to calculate a parameter that will be fixed, and compare column values to that.

    Jared
    CE - Microsoft

Viewing 8 posts - 16 through 22 (of 22 total)

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