Results 1 to 6 of 6

Thread: Datetime issue

  1. #1
    Join Date
    Feb 2005
    Posts
    3

    Unanswered: Datetime issue

    I am trying to run a query to get items that expire within the next 3 days. I got this off of another section of this website, but it will not work in Microsoft SQL Server 2000-Any ideas? Thanks.

    SELECT *
    FROM INVENTORY_TBL
    WHERE PERISHABLE_DATE BETWEEN DATE_ADD
    (CURRENT_DATE, INTERVAL 3 DAY) AND CURRENT_DATE

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to look up the syntax of DATEADD in Books Online.

    SELECT *
    FROM INVENTORY_TBL
    WHERE PERISHABLE_DATE BETWEEN DATE_ADD
    (day, 3, CURRENT_DATE) AND CURRENT_DATE
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2005
    Posts
    3
    Okay-didn't think to do that-I had just done a datetime search-Would I be correct that the following would return anything within 7 days?

    SELECT *
    FROM INVENTORY_TBL
    WHERE PERISHABLE_DATE BETWEEN DATEADD (DD, 7, GETDATE()) AND GETDATE()

    It isn't returning any data, so if I am doing correctly, I have something wrong in my insert statement.

    Thanks.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wherever you got that from, it doesn't work

    not even if you convert the syntax to sql server syntax

    the reason is, the dates need to be reversed

    this will not work --

    ... where perishable_date between dateadd(day,3,current_date) and current_date

    this might --

    ... where perishable_date between current_date and dateadd(day,3,current_date)

    the reason i say "might" is because you need to find what microsoft sql server uses instead of the standard sql current_date function

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whoops, you already found GETDATE() -- congratulations!

    now just change your query around and you'll be in business
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2005
    Posts
    3
    I switched the dates and it works. Thank you.

    SELECT *
    FROM INVENTORY_TBL
    WHERE PERISHABLE_DATE BETWEEN GETDATE() AND DATEADD(DD, 7, GETDATE())

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •