Select from Oracle table, date time value?

760 views Asked by At

I want to get the date time value from oracle tables. This is my code

$conn = oci_connect("xxxx", "xxxx", "xxxx");
$query_1 = "select max (p.navi_date), p.navi_user
from payments p
where p.pay_date >= trunc(sysdate)
and p.navi_user = 'HAS:WWW:PARDOXT'
group by  p.navi_user
order by 1";
$s_1 = oci_parse($conn, $query_1);
oci_execute($s_1);
//oci_fetch_all($s, $res);
oci_fetch_all($s_1, $arr_1);
$o_1 = $arr_1['MAX(P.NAVI_DATE)'];
echo($o_1[0]);

but it shows 11.06.15 without time. How can I select with time it?

1

There are 1 answers

7
Lalit Kumar B On BEST ANSWER

max (p.navi_date)

To display the date in your desired format, use TO_CHAR along with proper format model.

For example,

SQL> SELECT to_char(SYSDATE, 'MM/DD/YYYY HH24:MI:SS') dt FROM dual;

DT
-------------------
06/11/2015 14:28:04

SQL>

Your modified query would look like:

SELECT to_char(max(p.navi_date), 'MM/DD/YYYY HH24:MI:SS')...

NOTE TO_CHAR converts DATE into STRING to display the datetime value in your desired format. For date arithmetic, you should leave the date as it is, use to_char only to display as a string, not for date arithmetic.