« How to get the SQL that Perl’s DBI is running
[Perl|Ruby|Python|PHP] Golf »

Set the format of ‘Date’ fields returned by Oracle

For some reason Oracle returns the date from ‘date’ fields in the format ‘dd-short_month_name-yy’ (or something like that) by default. This is crazy, who uses the date in that format? No-one, that’s who. To change the format of dates returned for all queries returned to a database handle do something like this:

$dbh->do("alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'");

(or the equivalent in your language of choice) and the date returned from any date fields will be sensible, much like the way postgres does it. And why does Oracle not allow the use of limit? I guess this comes as a £10,000 upgrade or something.

This entry was posted on Wednesday, July 19th, 2006 at 9:36 pm and is filed under General, Perl, Programming. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “Set the format of ‘Date’ fields returned by Oracle”

  1. Carl Says:
    July 22nd, 2006 at 8:15 am

    SELECT * FROM table WHERE ROWNUM

  2. Carl Says:
    July 22nd, 2006 at 8:15 am

    Spazzy.

    SELECT * FROM table WHERE ROWNUM<=10;

  3. Will Says:
    July 22nd, 2006 at 9:36 am

    I still don’t like Oracle ;)

  4. CarlJ Says:
    July 28th, 2006 at 10:02 am

    That’s the default out of the box date, which is probably for compatibility back to the 1920s or something. I think the DBA can change the default date, from what I’ve seen they tend not to bother.

    Also ROWNUM gets more fun if you want to return 11 through 20. Something like:

    SELECT * FROM (SELECT *,ROWNUM as row FROM table) WHERE row BETWEEN (11,20)

Leave a Reply