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

1

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;
}
?>

answered Aug 28, 2013 at 17:39

Ali

3,4364 gold badges19 silver badges31 bronze badges

1 Comment

user1652319 Over a year ago

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

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

0

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.

answered Aug 28, 2013 at 17:41

rink.attendant.6

46.7k64 gold badges111 silver badges161 bronze badges

Comments

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

0

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;
    }

answered Aug 28, 2013 at 17:51

techierishi

3903 silver badges14 bronze badges

Collectives™ on Stack Overflow

PHP date formatting fails with MySQL DATE value

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related