View Full Version : Date conversion

10-29-2007, 12:13 AM
I'm displaying a list of events read from the database.
I used the following to make it 'more readable'

$query = "SELECT DATE_FORMAT(datum, '%a %d %M %Y') as dedag FROM optredens WHERE datum > now() ORDER BY datum ASC";
That gives me Sat 03 May 2008

I would like this to be in Dutch (Za 03 mei 2008)
Thought using strftime and setlocale would do the trick.
This is what I came up with:

$query2 = "SELECT datum FROM optredens WHERE datum < now() ORDER BY datum DESC";
$result2 = mysql_query($query2);

if ( !$result2 ) {
echo "error oprning database...<br>";
} else {
$numrows = mysql_num_rows($result2);
if ( 0 == $numrows ) {
echo "no entries found...";
} else {
echo "<span class='gigtitel'>Het verleden</span><br><br><table width='90%' border='0' align='center' id='optr'>";
echo "<tr id='row1'>
setlocale(LC_TIME, 'dutch');
for ( $j=0; $j < $numrows; $j++ ) {

$datum = mysql_result ( $result2, $j, "datum");
$nummer = $numrows-$j;
$dedag = strftime("%a %d %b %Y", $datum);

<tr><td class='optredentekst'><?php echo $nummer ?></td>
<td class='optredentekst'><?php echo $dedag ?></td>

Result: It's in Dutch! but not the data from the database :confused:

21 wo 31 dec 1969
20 wo 31 dec 1969
19 wo 31 dec 1969
18 wo 31 dec 1969
17 wo 31 dec 1969
16 wo 31 dec 1969

So I must be close to the solution.
What am I doing wrong??

10-29-2007, 01:40 AM
strftime() requires a unix timestamp to work with, so try changing that from the DB ..

"SELECT UNIX_TIMESTAMP(datum, '%a %d %M %Y') as dedag"

unix timestamps only work with dates >= 1970 (or in your case 31/12/69 ;)) so if you have dates earlier than that you can try setting the locale in MySQL

SET lc_time_names = 'nl_NL';

10-29-2007, 02:07 AM
got an error, but this worked


Thx!!!! :thumbsup:

10-30-2007, 02:01 AM
DOH, sorry, forgot to remove the formatting which is not an option in UNIX_TIMESTAMP(), glad you got it sorted.