Current Date issue in where clause

  • I am trying to write a query that pulls our new orders for the current day.

    Where FM.Openeddate = getdate()

    did not work. It took me an hour before randomly doing this

    Where FM.Openeddate >= getdate()

    and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.

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


    I am trying to write a query that pulls our new orders for the current day.

    Where FM.Openeddate = getdate()

    did not work. It took me an hour before randomly doing this

    Where FM.Openeddate >= getdate()

    and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.

    Function getdate() is retrieving the current date in the following format 2012-02-22 16:59:27.031. Check which values are exists in the field Openeddate of your table. You can see more details here -

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


    I am trying to write a query that pulls our new orders for the current day.

    Where FM.Openeddate = getdate()

    did not work. It took me an hour before randomly doing this

    Where FM.Openeddate >= getdate()

    and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.

    Are you sure that this is working for you? I would think what you really need something more like this:

    WHERE FM.Openeddate >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

  • Try this :

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

    Jeremy... (2/22/2012)


    I am trying to write a query that pulls our new orders for the current day.

    Where FM.Openeddate = getdate()

    did not work. It took me an hour before randomly doing this

    Where FM.Openeddate >= getdate()

    and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.

  • Lynn Pettis (2/22/2012)


    Jeremy... (2/22/2012)


    I am trying to write a query that pulls our new orders for the current day.

    Where FM.Openeddate = getdate()

    did not work. It took me an hour before randomly doing this

    Where FM.Openeddate >= getdate()

    and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.

    Are you sure that this is working for you? I would think what you really need something more like this:

    WHERE FM.Openeddate >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

    I'm fairly new to this. What would that statement do?

  • Hakuna Matata (2/22/2012)


    Try this :

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

    Jeremy... (2/22/2012)


    I am trying to write a query that pulls our new orders for the current day.

    Where FM.Openeddate = getdate()

    did not work. It took me an hour before randomly doing this

    Where FM.Openeddate >= getdate()

    and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.

    This worked. Thank you! Why exactly did i have to do this in the first place?

  • GETDATE() will retrieve date in the format 2012/02/22 21:23:33.256 (Includes Date & Time). So in your query when u say

    FM.Openeddate=getdate() the date match should be exact up to the micro seconds ie, you are trying to do a date & time match.

    whereas CONVERT(DATE,GETDATE()) will get you (2012/02/22) in date format not as DATETIME .

    Hope I am clear:-)

    Jeremy... (2/22/2012)


    Hakuna Matata (2/22/2012)


    Try this :

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

    Jeremy... (2/22/2012)


    I am trying to write a query that pulls our new orders for the current day.

    Where FM.Openeddate = getdate()

    did not work. It took me an hour before randomly doing this

    Where FM.Openeddate >= getdate()

    and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.

    This worked. Thank you! Why exactly did i have to do this in the first place?

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


    Lynn Pettis (2/22/2012)


    Jeremy... (2/22/2012)


    I am trying to write a query that pulls our new orders for the current day.

    Where FM.Openeddate = getdate()

    did not work. It took me an hour before randomly doing this

    Where FM.Openeddate >= getdate()

    and now it works. Any idea why pulling information for todays date needs to be >= and not just =? Thanks for your help.

    Are you sure that this is working for you? I would think what you really need something more like this:

    WHERE FM.Openeddate >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

    I'm fairly new to this. What would that statement do?

    I don't have your from statement, so you will have to complete the following to see what is going on:

    select top 20

    FM.Openeddate,

    GETDATE(),

    DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

    FROM

    dbo.yourtable FM

    ;

    After looking at the returned data, if you still have questions please feel free to ask.

  • getdate() retrieves the time too. So unless your orders were placed at this second, they're not going to return in the results section

  • The replie is quite simple when you go with finding the date as getdate(), the getdate will return you as 2012-02-22 10:03:50.033 if you look carefully it include yyyy-mm-dd hh:mm:ss.nano sec so when you search this value into your table you never gonna find the data tho the date is available for the givin date.

    And when you done columnname >= getdate() it means any value equall or greater then (2012-02-22 10:03:50.033) this.. thus you may get few result but still few data may get skip due to the time below your given getdate()

    If you convert the function as select convert(varchar(10),getdate(),103) you may get the just the date and you may compare directly with your table column and get the data for given date.

    Thanks & Regards
    Syed Sami Ur Rehman
    SQL-Server (Developer)
    Hyderabad
    Email-sami.sqldba@gmail.com

  • Thank you all for the explanations. Makes perfect sense now. 🙂

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

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

  • 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

    ;

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

Viewing 15 posts - 1 through 15 (of 22 total)

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