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!

Community · Accepted Answer · 2020-06-20 09:12:55Z

8

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 10, 2010 at 9:13

Vincent Malgrat

68k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ThinkJet Over a year ago

+1 Look at return values description here: php.net/manual/en/function.oci-fetch-array.php

Collectives™ on Stack Overflow

Retrieve dates from Oracle DB

1 Answer 1

Update

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related