Mysql Text to Date won't work

Question

I got the following problem. I got a mysql field which is type "text" and there are many birthday dates in it. I want to print the dates out and sort it in my way of date format. I can't change the type in the database because it's related to some user profile value fields.

The value's thats in the database are such this:

1978-12-31 23:59:59

This is my query:

$result = mysql_query("SELECT a.value, b.username from yrt6h_community_fields_values a join yrt6h_users b on a.user_id = b.id where a.field_id = 3 order by a.value;");

And this my example php file:

<table>
<tr>
<td>User:</td>
<td>Datum:</td>
</tr>
<?php
while($row = mysql_fetch_array($result)){
    echo '<tr>';
    echo '<td>';
    echo $row['username'] . ' : ';
    echo '</td>';
    echo '<td>';
    echo $row['value'];
    echo '</td></tr>';  
}
?>

</table>

I tried all of the date format functions of mysql, but then i got nothing. For example I tried this:

mysql_query("SELECT str_to_date(a.value, '%d.%m.%Y'), ...

or

mysql_query("SELECT str_to_date(date_format(a.value, '%Y.%m.%d') %d.%m.%Y), ...

But I do that, the echo of the date is empty. Theres nothing to print out anymore. But why? Even if I put this query directly in the database I got NULL for the birthday value.

Answer 1

why dont you try to do it with php like -

echo date('d-m-Y', strtotime($yourDate));//the first paramete is the date format you want and the second is the value you are getting from database.

Reference for more understanding, example and formats.

Answer 2

Almost there!

I would suggest that you first get the data from the db with

mysql_query("SELECT str_to_date(a.value, '%d.%m.%Y')

Then

while($row = mysql_fetch_array($result))
{
    $date = $row['date'];
    $time = strtotime($date);
    $formattedDate = date('Y-m-d', $time);
}

echo $formattedDate ;

Does that make senese?

Answer 3

Have you tried the PHP Date() function?

You could do something like so: If you're looking for all records that match a specific date:

$timestamp = date('Y-m-d H:i:s'); //get current timestamp
mysql_query("SELECT * FROM `table` WHERE `timestamp` = '$timestamp'");

Or -- If you're trying to select/order by Timestamp:

mysql_query("SELECT * FROM `table` ORDER BY `timestamp`"); //select all order by timestamp

Answer 4

you can use mysql "cast" function. rewrite the query in such a way: "SELECT cast(a.value as DATETIME) new_dob,str_to_date(a.value, '%d.%m.%Y') dob from table_nm where cond"