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.

4 thoughts on “Set the format of ‘Date’ fields returned by Oracle

  1. Carl says:

    SELECT * FROM table WHERE ROWNUM

  2. Carl says:

    Spazzy.

    SELECT * FROM table WHERE ROWNUM<=10;

  3. Will says:

    I still don’t like Oracle ;)

  4. CarlJ says:

    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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.