Date Format change in query

[hemi519]

Hi All,



I am facing a issue in changing the date format of a column in my database. Below is my 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




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?

[james438]

Date is stored in the database in the format YYYY-MM-DD ref (http://dev.mysql.com/doc/refman/5.5/en/datetime.html). There can't be any slashes if your date is stored in that format. In the example you gave

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
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.

[bernie1227]

very good answer James, you may want to also see here (http://www.java2s.com/Tutorial/MySQL/0280__Date-Time-Functions/STRTODATEstrformat.htm") for more information.

[james438]

I think you mean here (http://www.java2s.com/Tutorial/MySQL/0280__Date-Time-Functions/STRTODATEstrformat.htm) ;). It is handy, but I find dev.mysql.com (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date) to be better here.