SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    MYSQL DATE COMPARISON

    Hello,

    My below query does not product any result. i just want to know wether the comparison i am doing is correct.

    table : sms_logs

    to|date
    ------------------------------------
    05244546 | 2013/04/30 08:46:42 AM
    05244536 | 2013/02/10 08:46:42 AM
    05244536 | 2013/02/10 08:46:42 AM
    -----------------------------------

    PHP Code:
    Date1 '2013-04-01'
    Now i want to pull all the sms sent for the past 4 months from the Date1.

    PHP Code:
               $query1="SELECT * FROM sms_logs 
                        WHERE STR_TO_DATE(LEFT(date,10),'%Y/%m/%d') > DATE_SUB(
    $date1,INTERVAL 180 DAY)"

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try this --
    Code:
    WHERE DATE(date) > $date1 - INTERVAL 180 DAY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    try this --
    Code:
    WHERE DATE(date) > $date1 - INTERVAL 180 DAY
    0 Result it shows even though there must be records.

    sample query out put :
    SELECT * FROM sms_logs WHERE DATE(date)>2013-05-01 - INTERVAL 180 DAY

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    oh, i see what went wrong

    put quotes around the date string
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    oh, i see what went wrong

    put quotes around the date string
    did. but still showing 0

    sample query print :
    SELECT * FROM sms_logs WHERE DATE('date')>2013-05-01 - INTERVAL 180 DAY

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, man, not around the column name (that makes it a string), quotes around the date

    2013-05-01 is an arithmetic expression, 2013 minus 5 minus 1, which equals 2007

    '2013-05-01' is a proper date string
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, man, not around the column name (that makes it a string), quotes around the date

    2013-05-01 is an arithmetic expression, 2013 minus 5 minus 1, which equals 2007

    '2013-05-01' is a proper date string
    Absolutely! works charm!!
    Thank you r973 for your valuable time spend to solve my issue.

  8. #8
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    And now my query also working
    Thank you.

    Code:
    	       $query1="SELECT * FROM sms_logs 
    		            WHERE 
                                STR_TO_DATE(LEFT(date,10),'%Y/%m/%d') > DATE_SUB('$date1',INTERVAL 180 DAY)";

  9. #9
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    try this --
    Code:
    WHERE DATE(date) > $date1 - INTERVAL 180 DAY
    i see in mysql manual here that YYYY-MM-DD HH:MM:SS is a valid date format and did not find dates separated with /s are shown as valid examples.

    now in your example above your converting a date seperated by /s to DATE(). so i am confused

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by afridy View Post
    i see in mysql manual here that YYYY-MM-DD HH:MM:SS is a valid date format
    yes, that's a valid format for date string values that are used, for example, in sql statements

    however, internal date storage format is completely different

    you should not be using STR_TO_DATE(), you should be using DATE()
    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
  •