Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: Need To CalcuThe Number Of Days Between The Current Date And A Stored Date

    I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes it can be done through a query, but the standard sql for it will almost certainly not work in whatever database system you're using

    date functions are notoriously non-standard, and each database system pretty much has its own proprietary functions

    if you wouldn't mind mentioning which database system you're using, we could probably help you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2005
    Posts
    91
    Nevermind I figured it out. I am using a SQL server 2000 database to query my data. I used the Datediff() function in conjunction with getdate(). See examples below for anyone else needing help with this topic.

    DATEDIFF([day], dbo.table.field, GETDATE()) AS DAYS,
    DATEDIFF([HOUR], dbo.table.field, GETDATE()) AS HOURS

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moved to SQL Server forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The solution you arrived at will work, but will not benefit from any indexing on your dbo.table.field column. If, instead, you used the DateAdd() function to find the date five days prior to the current date, then the optimizer will be able to use an index on dbo.table.field for comparisons:
    Code:
    Where dbo.table.field >= DateAdd(day, -5, GetDate())
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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