Good Day!

I had problem in displaying datetime using select statement.

I have TimeIn and TimeOut with datatype DateTime

I want to display it (dd-mm-yyyy) and the hours is 12 hours with AM and PM.

now it display:

TimeIn: 2011-10-24 05:35:00
TimeOut: 2011-10-24 13:35:00

this is my code:

$sql = "SELECT EMP_NO, TimeIn, TimeOut FROM attendance WHERE Date BETWEEN '$DATE1' AND '$DATE2'";

I want to display the data
TimeIn: 24-10-2011 05:35 AM
TimeOut: 24-10-2011 014:35 PM

I tried to find syntax for that but it did not match on what format i want.

Thank you
Thank you

    newphpcoder;10989755 wrote:

    I tried to find syntax for that but it did not match on what format i want.

    What DBMS are you using and where did you look?

      Have you looked into the PHP date() function? It should do exactly what you're looking for.

        My database is mysql

        I tried this:

         $sql = "SELECT EMP_NO, TimeIn, date('$TimeOut', 'd-m-y g:i:s:A')  FROM attendance WHERE Date BETWEEN '$DATE1' AND '$DATE2'";

        But no data display

        Thank you

          This is my original code with datetime format:

          <?php

include 'config.php';

 if(!isset($_POST['submit_'])){ 

$DATE1 = $_POST['firstinput'];
$DATE2   = $_POST['secondinput'];


 $sql = "SELECT EMP_NO, TimeIn, TimeOut  FROM attendance WHERE Date BETWEEN '$DATE1' AND '$DATE2'";

$attendance = $conn->GetAll($sql);
$smarty->assign('attendance', $attendance);

 }
 $smarty->display('header.tpl');
 $smarty->display('left.tpl');
 $smarty->display('empAttendance.tpl');
 $smarty->display('footer.tpl');

?>

          empattendance.tpl:

          



<div class="income" style="width: 550px; height: 190px; overflow: auto; padding: 5px">
<fieldset>
<legend>Employee Attendance</legend>
<table border="1">
<tr>
<td colspan="2" style="text-align:center">Employee No</td>
<td colspan="2" style="text-align:center">Date/Time In</td>
<td colspan="2" style="text-align:center">Date/Time Out</td>
</tr>

{section name=att loop=$attendance}
  <tr>
    <td colspan="2">{$attendance[att].EMP_NO}</td>
    <td colspan="2">{$attendance[att].TimeIn}</td>
    <td colspan="2">{$attendance[att].TimeOut}</td>  
          
  </tr>
{sectionelse}
  <tr><td colspan="1">No DATA</td></tr>
{/section}
</table>
</fieldset>
</div>
            Bonesnap;10989800 wrote:

            date function? It should do exactly what you're looking for.

            No it should not.

            newphpcoder;10989819 wrote:

            My database is mysql

             $sql = "date('$TimeOut', 'd-m-y g:i:s:A')";

            And it doesn't even take two parameters (which means the above should have given an error message).

            Use MySQL's online documentation. For example, DATE states that

            MySQL documentation wrote:

            DATE(expr)

            Extracts the date part of the date or datetime expression expr.

            mysql> SELECT DATE('2003-12-31 01:02:03');
            -> '2003-12-31'

            which makes it clear that the function takes exactly one parameter and gives no way of formatting the date at all.

            DATE_FORMAT on the other hand allows you to format a date/datetime any way you like.

              johanafm;10989834 wrote:

              No it should not.

              Why not? date() should be able to format a date to look exactly like "24-10-2011 05:35 AM" no problem.

              echo date('d-m-Y h:i A', $somedate);

                I was assuming you were talking about the SQL date function since he has only posted SQL code so far. And he was also trying to use the SQL date function.

                  I used this code and it works.

                  <td colspan="2">{$attendance[att].TimeOut|date_format:"%d-%m-%Y %I:%M %p"}</td>
                    Write a Reply...