[Resolved] Convert Military Time to Standard Time

[lyndonlegend]

Does anyone know a good way to convert the military time retrieved from the mysql table to have it display as standard time on the webpage. If you view

www.reedsburgpolars.com/homeice_schedule.php

you would see the game times as military time. I would like to display these as a AM or PM time.

Thanks.

[mrhappiness]

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

[lyndonlegend]

Thanks. I may be blind, but I did not find an area which actually took a field whose length was defined as TIME and converted it.

[mrhappiness]

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html#id2902100 (DATE_FORMAT)

you are blind 😉

[lyndonlegend]

I am trying to convert the format of my date and time from mysql table when it appears on the screen. I thought I had the code correct in the select statement, but when it comes up on the screen http://www.reedsburgpolars.com/homeice_schedule.php the date and time are blank. Any help is greatly appreciated. This is the code I am using:

  <?php
    $host="localhost";
    $usrnm="xxxxx";
    $passwrd="xxxxx";
    $database="xxxxx";
    $tablename="homeice";
    $day=$_POST["day"];
    $date=$_POST["date"];
    $gametime=$_POST["gametime"];
    $reedsburgteam=$_POST["reedsburgteam"];
    $opponent=$_POST["opponent"];
    $gametype=$_POST["gametype"];

    $con= mysql_connect($host,$usrnm,$passwrd)
      or die("Connection failed!!! <br>");

    $varselect=mysql_select_db($database)
      or die("Database selection failed!!!");

    $fetch = mysql_query("SELECT day, DATE_FORMAT(date,'%m.%d.%Y'), TIME_FORMAT(gametime, '%l %i %r'), reedsburgteam, opponent, gametype FROM homeice ORDER BY date, gametime")
      or die(mysql_error());

    $num = mysql_num_rows($fetch);

    if (empty($num))
      die('No records found');

    echo '
      <table border="4" width="100%" cellpadding="4">
        <tr align="center">
          <td>
            <b>DAY</b>
          </td>
          <td>
            <b>DATE</b>
          </td>
          <td>
            <b>GAME TIME</b>
          </td>
          <td>
            <b>REEDSBURG TEAM</b>
          </td>
          <td>
            <b>OPPONENT</b>
          </td>
          <td>
            <b>GAME TYPE</b>
          </td>
        </tr>';

    while ($row = mysql_fetch_assoc($fetch))
    {

    if ($row['reedsburgteam'] == "BANTAM")
    {
     $bgcolor="#AFEEEE";
    }
     else
     if  ($row['reedsburgteam'] == "BOYS JUNIOR VARSITY")
    {
     $bgcolor="#FFE4B5";
    }
     else
    {
     $bgcolor="#1E90FF";
    }

    echo '
        <tr bgcolor="'.$bgcolor.'">
          <td>',$row['day'],'</td>
          <td align="center">',$row['date'],'</td>
          <td align="center">',$row['gametime'],'</td>
          <td align="center">',$row['reedsburgteam'],'</td>
          <td align="center">',$row['opponent'],'</td>
          <td align="center">',$row['gametype'],'</td>
        </tr>';
    }

    echo '
      </table>';
  ?>

[devinemke]

change your query to:

SELECT day, DATE_FORMAT(date,'%m.%d.%Y') AS date, TIME_FORMAT(gametime, '%l %i %r') AS gametime, reedsburgteam, opponent, gametype FROM homeice ORDER BY date, gametime

[lyndonlegend]

Great that fixed the issue.