Automatically format sql dates

Question

What I'm trying to do is automatically change the output format of dates in my sql queries without having to specify it (PHP/Oracle).

I'm using to_char(pi.date_of_birth , 'YYYY/MM/DD') as date_of_birth to change the formatting of my queries. What I'd like to be able to do is apply this to any date returned by the '*' operator.

My problem is that I can't write all of my queries by hand, most of them are generated dynamically, and it would be much easier to change the format globally some way.

Now the problem I also have is that I have 'zero' control over the database. So I can't go in and change anything, or add any procedures. Is there some syntax I can append to my queries, or a simple way of integrating it into php? Thanks.

Answer 1

Why do you need to format the output in the database layer at all? Why not retrieve it as whatever type PHP uses for dates and times, and format it there? Likewise for input values, you should use parameterised queries and specify the values as dates/times rather than putting them into the SQL in a particular format.

Answer 2

if you really need to change the format, modify your connection script to add an alter session that will change the NLS_DATE_FORMAT

$db = new PDO("oci:dbname=" . $dbSource, $dbUser, $dbPass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD'";
$db->exec($sql);