Convert oracle date to another date format using php language-CodePudding

I am extracting date from the oracle database in this format: 31-DEC-68, then I use PHP to convert it to date like this: 'Y-m-d'. it's working fine for me for dates after 2000 otherwise it gives a wrong date for example this date :31-DEC-68 converted to 2068-12-31, it should be 1968-12-31.

this is a sample of my code:

$query = "select * from my_table";
$stid = oci_parse($connection, $query);
oci_execute($stid);
while (oci_fetch($stid)) {  
    $dateVariable = oci_result($stid, 'date_field_from_oracle'); 
}

Any help, please?

CodePudding user response：

it worked for me finally, I used:

TO_CHAR(date_field_from_oracle, 'dd-mm-yyyy')

it gives the correct date format: 31-12-1968

Thanks a lot

CodePudding user response：

Assuming for a moment that the date column is really stored as a DATE in the database, don't use select * in your query: this is generally considered sloppy anyway, and in your case in particular you should be using the to_char() function to explicitly define the format in which you want the date returned.

Try something like the following:

select to_char(date_field,'YYYY-MM-DD'), other_field1, other_field2 from my_table

Then you shouldn't have to do an additional format conversion in PHP or worry about default session parameters. In general you should always define your expected columns and return formats explicitly in your SQL, as default formats may change between tools and sessions (as you're seeing already - that's most likely why the DBAs query results look different that yours), and to be able to identify which code is directly affected by changes to the underlying table (like changes to column names, addition or removal of columns, etc.).