Retrieve dates from Oracle DB

Question

I'm using an Oracle DB and I'm trying to fetch data from its tables using PHP. One of the tables contains a date column which behaves strange to me.

When I open the table in my DB client (I'm using Navicat Lite) I see dates like "2007-11-29 10:15:42" but when I retrieve them with PHP and display the date it says "29-NOV-07". I use a simple SQL query and standard PHP functions (oci_parse, oci_execute, oci_fetch_array).

Why is the value from the DB converted to this (useless) format? How can I get the date just like it is stored in the DB? Thanks for your tips!

Answer 1

Oracle DATE datatype is a point in time, it has no format attached. When you transform a date to a string (to display if for example), the format applied to the date will be dependent upon your session parameters (implicit conversion). From what I remember of PHP, the retrieval functions will convert the date to a string automatically, using the NLS_DATE_FORMAT session parameter.

Either:

• change the NLS_DATE_FORMAT beforehand with:

  ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
  

• or, if you want to specify another format in your query, you should explicitely ask for it:

   SELECT to_char(my_date, 'yyyy-mm-dd hh24:mi:ss') ...
  

---

Update

Thanks to ThinkJet for the link to the PHP documentation:

DATE columns are returned as strings formatted to the current date format. The default format can be changed with Oracle environment variables such as NLS_LANG or by a previously executed ALTER SESSION SET NLS_DATE_FORMAT command.