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. 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 for more information.

[james438]

I think you mean here . It is handy, but I find dev.mysql.com to be better here.