convert mysql date

[ayushchd]

Hi I am Using the following function to convert mysql date format into my req.

function date_convert($date){

$date_year=substr($date,0,4);
$date_month=substr($date,5,2);
$date_day=substr($date,8,2);
$date=date("F jS, Y", mktime(0,0,0,$date_month,$date_day,$date_year));
return $date;
}

echo date_convert($row['date']); //// $row['date'] is in the form :September 11, 2007

This echoes nothing. Can you help me out?

[dreamcatcher]

Hi ayushchd,

If all you want to do is convert the mysql date, use DATE_FORMAT [dev.mysql.com]. Let the database do the work.

dc

[ayushchd]

$date = "SELECT DATE_FORMAT('date', '%m %e %Y') from table;";
if (!mysql_query($date)) {
echo mysql_error();
}
$date_row = mysql_fetch_assoc(mysql_query($date));

echo $date_row['date'];
Doesn't Work.

I tried execute this in phpmyadmin too.
SELECT DATE_FORMAT('date', '%m %e %Y') from table

It returned :

--------------------------------------------
DATE_FORMAT( 'date' , '%m %e %Y' )
--------------------------------------------
NULL
--------------------------------------------
NULL
--------------------------------------------
NULL
--------------------------------------------

[dreamcatcher]

$date = "SELECT DATE_FORMAT(date, '%m %e %Y') AS new_date FROM table";
if (!mysql_query($date)) {
echo mysql_error();
}
$date_row = mysql_fetch_assoc(mysql_query($date));

echo $date_row['new_date'];

dc

[ayushchd]

Is it necessary for the date column's date type to be Date only?

[dreamcatcher]

No, date format will work on any date column.

dc

[ayushchd]

$date = "SELECT DATE_FORMAT('date', '%m %e %Y') AS new_date FROM shares;";
if (!mysql_query($date)) {
echo mysql_error();
}
$date_result = mysql_query($date);

$date_row = mysql_fetch_assoc($date_result);

echo $date_row['new_date'];

Even This doesn't work. Nothing is echoed. No mysql error is displayed either.

The 'date' column's data type is TEXT and the date is in the form of :

September 24, 2007

[Habtom]

Try the following:

$date = "SELECT DATE_FORMAT('date', '%m %e %Y') AS new_date FROM shares";

$date_result = mysql_query($date);
$date_row = mysql_fetch_assoc($date_result);

print_r($date_row)

echo $date_row['new_date'];

I have removed an extra ";" in your query.
I have removed the mysql_query (even in the if condition it does execute, I believe)
Added print_r, to see if you are having any data in the array.

Habtom

[ayushchd]

No data in array...it showed the following four arrays which is bcoz i have four rows in the table......

Array
(
[new_date] =>
)

Array
(
[new_date] =>
)

Array
(
[new_date] =>
)

Array
(
[new_date] =>
)

[Habtom]

Yea, ofcourse

$date_row = mysql_fetch_assoc($date_result);
print_r($date_row)
echo $date_row['new_date'];

Replace with . . .

while ($date_row = mysql_fetch_array($date_result, MYSQL_ASSOC)) {
echo $date_row['new_date'];
}

Habtom

[ayushchd]

Nothing is echoed.

[ayushchd]

Please help me

[Habtom]

What does your current code look like?

[ayushchd]

$date = "SELECT DATE_FORMAT('date', '%m %e %Y') AS new_date FROM shares";

$date_result = mysql_query($date);
while ($date_row = mysql_fetch_array($date_result, MYSQL_ASSOC)) {
echo $date_row['new_date'];
}

date column - data type - varchar - format - September 27, 2007

[dreamcatcher]

Try removing the apostrophes around the field name:

DATE_FORMAT(date, '%m %e %Y')

dc

[ayushchd]

Doesnt work even now. Does the datatype = 'varchar'
make a difference?

[jatar_k]

if you look through tthe comments on the page dreamcatcher linked to you will find this

If you have a table1 , and (fields date which is varchar(100) you can also convert it as date type look the following example

mysql> select str_to_date(date,'%d/%m/%Y') as Mydate from table1 order by Mydate DESC;

maybe try something like that

[ayushchd]

Hey finally done......

I got rid of mysql function and used php to do the conversion

$query= "select * from listedcomp";
$result = mysql_query($query);
while ($rows = mysql_fetch_assoc($result)) {
$date = $row['date'];
$date = strtotime($date);
$date = date('d/n/y', $date);
echo $date;
}

ayush