Results 1 to 4 of 4

Thread: Date Format change in query

  1. #1
    Join Date
    Nov 2010
    Posts
    114
    Thanks
    27
    Thanked 0 Times in 0 Posts

    Default Date Format change in query

    Hi All,



    I am facing a issue in changing the date format of a column in my database. Below is my code


    PHP Code:
    $fromdate $_REQUEST['fromdate']; // this date is in format 22-06-2012
    $todate $_REQUEST['todate']; // this date is in format 22-09-2012

    $query mysql_query("select * from customer where joined_date >= '$fromdate' and joined_date <= '$todate'");

    // the above query is giving me null value because i stored the date as 22/08/2012(joined_date). The date format is different. I want to happen this in query only. 
    Below is something i am trying to change format in query, but not working

    PHP Code:

    SELECT date_format
    (joined_date,'%d-%m-%Y') as date,joined_date FROM customer where code '3016' 

    The above query is giving null value, how to solve the issue?

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,720
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    Date is stored in the database in the format YYYY-MM-DD ref. There can't be any slashes if your date is stored in that format. In the example you gave
    PHP Code:
    SELECT date_format(joined_date,'%d-%m-%Y') as date,joined_date FROM customer where code '3016' 
    date_joined is pulled twice and nothing more. If your date is stored in TEXT format like your code implies then you want to use STR_TO_DATE instead and place it to the right of the WHERE.

    PHP Code:
    <?php
    include 'dbconnect.php';
    $today='2013-08-23';
    $query "SELECT ID, date_joined FROM test WHERE STR_TO_DATE(date_joined,'%d/%m/%Y') <='$today';";
    $test  mysql_query($query);
    $test1 mysql_fetch_array($test,MYSQL_ASSOC);
    $test3 $test1['date_joined'];
    echo 
    "$test3<br><br>";
    print_r($test1);
    ?>
    STR_TO_DATE() converts the date_joined column and reads it in the DATE format or at least attempts to. Since your date_joined column uses slashes and is listed in the reverse format of DATE you need to tell STR_TO_DATE() to read it that way: STR_TO_DATE(date_joined,'%d/%m/%Y')

    Here's a hypothetical example to further demonstrate the usage of STR_TO_DATE(). If you stored it in TEXT format as 'date 08X23X2012' you would use STR_TO_DATE(date_joined,'date %mX%dX%Y') to read it.

    Your $fromdate and $todate should also be formatted into YYYY-MM-DD format. Your database column date_joined should be formatted into the date format if it isn't already. Be careful to back up your database first because you will probably need to re-enter the dates into your database. This may seem like a bit of a chore if you have many rows of data, but this can be simplified by writing a php script to do this for you.

    AS is also known as an alias. Basically you recognize a column name by another column name.
    Last edited by james438; 09-22-2012 at 06:53 PM. Reason: grammar
    To choose the lesser of two evils is still to choose evil. My personal site

  3. #3
    Join Date
    May 2012
    Location
    Hitchhiking the Galaxy
    Posts
    1,013
    Thanks
    47
    Thanked 139 Times in 139 Posts
    Blog Entries
    1

    Default

    very good answer James, you may want to also see here for more information.
    "Most good programmers do programming not because they expect to get paid or get adulation by the public, but because it is fun to program." - Linus Torvalds
    Anime Views Forums
    Bernie

  4. #4
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,720
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    I think you mean here . It is handy, but I find dev.mysql.com to be better here.
    Last edited by james438; 09-22-2012 at 06:50 PM.
    To choose the lesser of two evils is still to choose evil. My personal site

Similar Threads

  1. how to change date format in script
    By Benq in forum JavaScript
    Replies: 6
    Last Post: 08-16-2014, 09:25 PM
  2. Replies: 0
    Last Post: 10-20-2011, 08:32 AM
  3. Display Date format in Jason's Date Input Calendar
    By axiom28 in forum Dynamic Drive scripts help
    Replies: 0
    Last Post: 09-29-2011, 06:41 AM
  4. how to change date format ?
    By sabahan in forum PHP
    Replies: 5
    Last Post: 09-17-2009, 12:10 AM
  5. Universal Countdown Script - Change date format?
    By Anders_II in forum Dynamic Drive scripts help
    Replies: 1
    Last Post: 01-30-2009, 05:03 PM

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
  •