Oracle database date column does not show full date in php

1
votes

I have date column and date value is set the format : DD-MON-RR HH24:MI:SS and when I execute sql script from sqldeveloper I can see the date like 16-MAR-15 20:40:47. But when I want to get this value with php :

$currentDATE=$row['DATE'];
echo gettype($row['DATE']);
echo $currentDATE;

I see the result as string16-MAR-15 How can I see full date information in php page?

2
phporacledateoracle11g

2 Answers

3
votes

It will be due to the nls_date_format settings on your Oracle client. It's also a really good demonstration as to why your SQL scripts should not rely on implicit conversions.

You have two options:

  1. (recommended) Update your sql script so that the sql statement outputs to_char(<date column>, '<your date format>')
  2. alter your default nls_date_format setting by running alter session set nls_date_format= '<your date format>'; just after connecting to SQL*Plus.

The first option is by far the best option; it means anyone who has to run that script will see the same results, without having to faff around with the nls_date_format setting.

1
votes

I have date column and date value is set the format : DD-MON-RR HH24:MI:SS

Make sure that while displaying a DATE you explicitly convert the DATE into string using TO_CHAR along with the desired format mask.

The date format which you see, depends on your locale-specific NLS settings.

Remember,

  1. For date calculations, convert a string to date using TO_DATE. If it is a date type, then leave as it is.
  2. To display a DATE value in your desired format, convert it into a string using TO_CHAR and proper format mask.