SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Thread: compare dates

  1. #1
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    compare dates

    Hello

    I have a field that stores date in this way
    2011-05-07 12:35:47

    , and I want to retrieve all the fields are stored today and
    yesterday
    i tried to test query like this But it did not retrieve any fields

    Code SQL:
    SELECT * FROM Staff_logs WHERE  TIMESTAMP = CURRENT_DATE AND TIMESTAMP = CURRENT_DATE - INTERVAL 1 DAY

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Instead of 'TIMESTAMP' use the name of the column containing the timestamp.
    And the column can never be equal to today's date AND yesterdays date. Use BETWEEN instead:
    Code:
    SELECT * 
    FROM Staff_logs 
    WHERE columnname BETWEEN CURRENT_DATE AND CURRENT_DATE - INTERVAL 1 DAY

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    column name is timestamp

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Instead of 'TIMESTAMP' use the name of the column containing the timestamp.
    And the column can never be equal to today's date AND yesterdays date. Use BETWEEN instead:
    Code:
    SELECT * 
    FROM Staff_logs 
    WHERE columnname BETWEEN CURRENT_DATE AND CURRENT_DATE - INTERVAL 1 DAY
    i tried this query but still no results

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    I would change it. Timestamp is a reserved SQL word.
    Unless you want to use them awfull back-ticks all the time.

  6. #6
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i want to solve it without change column name, database for open source software

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by altarek View Post
    i want to solve it without change column name, database for open source software
    Put back-ticks around the column name.
    `
    Code SQL:
    SELECT * 
    FROM Staff_logs 
    WHERE `TIMESTAMP` BETWEEN CURRENT_DATE AND CURRENT_DATE - INTERVAL 1 DAY

  8. #8
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still the problem exists

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    your problem is the BETWEEN values

    it's the same as if i asked you which letters are between G and E

    they're in the wrong sequence
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    your problem is the BETWEEN values

    it's the same as if i asked you which letters are between G and E

    they're in the wrong sequence
    Oops, missed that

  11. #11
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i tried this but still problem exists

    Code MySQL:

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    your problem is the BETWEEN values

    it's the same as if i asked you which letters are between G and E

    they're in the wrong sequence
    Code SQL:
    SELECT * 
    FROM Staff_logs 
    WHERE `TIMESTAMP` BETWEEN CURRENT_DATE - INTERVAL 3 DAY AND CURRENT_DATE

  13. #13
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Code SQL:
    SELECT * 
    FROM Staff_logs 
    WHERE `TIMESTAMP` BETWEEN CURRENT_DATE - INTERVAL 3 DAY AND CURRENT_DATE
    Oooooooooh
    that's work good , thanks

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by altarek View Post
    Oooooooooh
    that's work good , thanks
    you're welcome
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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