Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Senior Coder
    Join Date
    May 2006
    Posts
    1,673
    Thanks
    28
    Thanked 4 Times in 4 Posts

    Date conversion Problem

    Hi,

    I have saved the "to be published" date in my table in this format:
    mm/dd/yyyy eg: 02/29/2012

    I now want to select those records that are available to publish.

    So I have this query:

    PHP Code:
    $now strtotime("now");
    $sql_sup "SELECT page_name FROM pages WHERE page_no = '$page_id' AND pub_date > $now"
    But that isn't going to work so can I use the strtotime() function inside my query eg strtotime("pub_date") ?

    If not is there a mysql equivilent ?

    Thanks.


    .
    If you want to attract and keep more clients, then offer great customer support.

    Support-Focus.com. automates the process and gives you a trust seal to place on your website.
    I recommend that you at least take the 30 day free trial.

  • #2
    Regular Coder
    Join Date
    Jan 2012
    Posts
    134
    Thanks
    0
    Thanked 32 Times in 32 Posts
    You can use Mysql's str_to_date() function to convert the string value to Mysql's special date format. After that, you can use unix_timestamp() or from_unixtime() on either one of your values to get them to be the same format.

    The first thing you should do, though, is convert your pub_date column to be the mysql "date" type. That's a better way to store the data and saves you a step.

    Also look at the now() function.

    Lots of stuff there, but here's the overview:

    1. Convert column pub_date from varchar to date
    2. Change query to this:

    PHP Code:
    $sql_sup "SELECT page_name FROM pages WHERE page_no = '$page_id' AND pub_date > NOW()"

  • #3
    Senior Coder
    Join Date
    May 2006
    Posts
    1,673
    Thanks
    28
    Thanked 4 Times in 4 Posts
    Hi,

    Thanks for the suggestion.

    If I change the column pub_date from varchar to date,
    do I need to change my format to save it ?

    At the present I just have:
    PHP Code:
    $Db_pub_date '20/02/2012';

    $sql "UPDATE `pages` SET pub_date   = '$Db_pub_date' WHERE page_id = '$Db_page_id' "
    Thanks.


    .
    If you want to attract and keep more clients, then offer great customer support.

    Support-Focus.com. automates the process and gives you a trust seal to place on your website.
    I recommend that you at least take the 30 day free trial.


  •  

    Posting Permissions

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