SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot Tikila's Avatar
    Join Date
    Jun 2007
    Location
    Toronto
    Posts
    173
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with a string date,Selecting the most recent.

    Selecting the last 10 days subscriptions.
    PHP Code:
    $Posted date("F/d/Y, g:i:s");
    $sql  mysql_query("SELECT id FROM Subscribers WHERE activated='1' AND DATE_SUB(CURDATE(),INTERVAL 10 DAY) <= Subscribers.Posted"); 
    If I turned Posted into a timestamp,it works.But I want to keep the date format as it is and still be able to perform that query.

    Any help is much appreciated.Thank you in a advance!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    dates in mysql need to be stated in year-month-day sequence

    always use DATE or DATETIME -- VARCHAR dates have many, many problems

    and this is one example
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot Tikila's Avatar
    Join Date
    Jun 2007
    Location
    Toronto
    Posts
    173
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    dates in mysql need to be stated in year-month-day sequence

    always use DATE or DATETIME -- VARCHAR dates have many, many problems

    and this is one example
    tought i was saved the hasstle of the date_format thing,now am at the very back of the queue.
    Bad planning=bad redoing

    So,if you wouldnt mind,once i have updated the field to a datetime,i can format it on retrieval like this:
    PHP Code:
    sql=mysql_query("select member_id,date_format(`Posted`, 'F/d/Y, g:i:s' ),activated"); 
    Correct?


    thank you Rudy for your readiness to help!
    Last edited by Tikila; Mar 16, 2008 at 01:02.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    not quite like that, but you are getting very close

    the DATE_FORMAT function takes as its second parameter a string composed of tokens which must each have a percentage sign

    example: DATE_FORMAT(posted,'&#37;M/%d/%Y %H:%i:%s')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot Tikila's Avatar
    Join Date
    Jun 2007
    Location
    Toronto
    Posts
    173
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    not quite like that, but you are getting very close

    the DATE_FORMAT function takes as its second parameter a string composed of tokens which must each have a percentage sign

    example: DATE_FORMAT(posted,'&#37;M/%d/%Y %H:%i:%s')
    thanks Rudy,your help is awsome and much appreciated !
    just one last quick tresspassing on your help
    what if the query is a join like:
    PHP Code:
    sql=mysql_query("select table1.member_id,table1.date_format(`Posted`,'%M/%d/%Y %H:%i:%s'),table1.activated,Category.id FROM table 1 LEFT JOIN ...."); 
    Of course,the above will throw an error,am just curious how it is done.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    my pleasure

    especially for a fellow ontarian
    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
  •