PHP date formatting fails with MySQL DATE value

Question

can anyone explain why the following code

function convdate($date) { $formatdate = strtoupper(date('M', $date )); $formatdate .= " " .date('d',$date). " "; return $formatdate; } $sql = "SELECT * FROM livedates ORDER by date"; $res = mysql_query($sql); while($livedates = mysql_fetch_array($res)){ $output= convdate($livedates['date']) . "<br>"; echo $output; }

Outputs :

DEC 31 DEC 31 DEC 31

when if i just output the date from the resultset using

$output= $livedates['date']. "<br>";

I get

2013-08-03 2013-08-10 2013-12-09

What I want to be getting is

AUG 03 AUG 10 DEC 09

Its going to be a mistake on my part , I realise that but i'm stumped !

Is the date column defined as timestamp or as datetime or as bigint in DB? What does var_dump($livedates['date']) show? — Ivan Hušnjak
– Ivan Hušnjak, Commented Aug 28, 2013 at 17:36
@IvanHušnjak It looks like it's a DATE, according to the second output. — rink.attendant.6
– rink.attendant.6, Commented Aug 28, 2013 at 17:38
@IvanHušnjak as DATE in mysql ...just realised I need to be doing strtotime — user1652319
– user1652319, Commented Aug 28, 2013 at 17:39

rink.attendant.6 · Accepted Answer · 2013-08-28 17:43:56Z

2

You need to convert $livedates['date'] to a unix timestamp before passing it to the date function. strtotime can do it:

$formatdate = strtoupper(date('M', strtotime($date) ));

edited Aug 28, 2013 at 17:43

rink.attendant.6

46.7k64 gold badges111 silver badges161 bronze badges

answered Aug 28, 2013 at 17:39

Scott Jungwirth

6,7353 gold badges42 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ali · Accepted Answer · 2013-08-28 17:39:20Z

What is wrong is with your function

The second parameter of date has to be a timestamp so this is the function that would work

<?php function convdate($date) { $date = strtotime($date); $formatdate = strtoupper(date('M', $date )); $formatdate .= " " .date('d',$date). " "; return $formatdate; } ?>

lol What to do when everyone answers the right answer ! Thanks all

rink.attendant.6 · Accepted Answer · 2013-08-28 17:41:39Z

Try changing your function to this:

function convdate($date) { $formatdate = new DateTime($date); return strtoupper($formatdate->format('M d')); }

The DateTime class is amazingly wonderful to use, I suggest you check it out.

techierishi · Accepted Answer · 2013-08-28 17:51:59Z

Your updated code should be as follows function convdate($date) { $formatdate = strtoupper(date('M', strtotime($date) )); $formatdate .= " " .date('d',strtotime($date)). " "; return $formatdate; } $sql = "SELECT * FROM livedates ORDER by date"; $res = mysql_query($sql); while($livedates = mysql_fetch_array($res)){ $strinoutput= convdate($livedates['date']) . "<br>"; echo $strinoutput; }

Collectives™ on Stack Overflow

PHP date formatting fails with MySQL DATE value

4 Answers 4

Comments

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Related