Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

DBI Date Formatting

by david.shin (Initiate)
on Jun 16, 2004 at 14:57 UTC ( #367258=perlquestion: print w/replies, xml ) Need Help??

david.shin has asked for the wisdom of the Perl Monks concerning the following question:

I have a record set from an oracle DB and one of the columns is a date/time field. Oracle spits me back DD-MMM-YY, but i need it in the format of YYYYMMDD.

I've googled it, but haven't turned up anything useful. Right now I have a regex that converts it for me, but it's not fool proff since I only have the last 2 digits of the year (i use a check of YY > 10, then 19YY else 20YY).

Is there a global setting for DBI that I can set so it knows to convert dates in a certain format?

Thanks, Dave

Replies are listed 'Best First'.
Re: DBI Date Formatting
by pelagic (Priest) on Jun 16, 2004 at 15:01 UTC
    You could use Oracle SQL as:
    select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual; or select to_char(sysdate, 'YYYYMMDD') from dual;
    To let Oracle do the formatting.

Re: DBI Date Formatting
by dba (Monk) on Jun 16, 2004 at 16:59 UTC
    Issue the following SQL after you connect:

    alter session set nls_date_format='YYYYMMDD';

    Then all dates returned will be of the format you require.
      You saved my life. :) alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'

      Thank you. Thank you. Thank you.

Re: DBI Date Formatting
by Happy-the-monk (Canon) on Jun 16, 2004 at 15:07 UTC

    Is there a global setting for DBI that I can set so it knows to convert dates in a certain format?

    no. You could have verified that yourself searching   perldoc DBI   for the string "date".
    If you do that in   perldoc DBD::Oracle, you can learn a bit more about the solution pelagic has offered you. His answer is the way to go (...ok, taking the string and manipulating it with perl works too...).

    Cheers, Sören

Re: DBI Date Formatting
by elwarren (Curate) on Jun 17, 2004 at 13:06 UTC
    Oracle handles y2k dates with RR instead of YY. Two digit years below 50 are presumed to be 20xx and above 50 are 19xx. Here's the docs and sample from oracle's site:
    SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR') ,'YYYY') "Year" FROM DUAL; Year ---- 1998 SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year" FROM DUAL; Year ---- 2017
    rr date handling and oracle date formatting. HTH

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://367258]
Approved by Happy-the-monk
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (1)
As of 2021-10-24 07:24 GMT
Find Nodes?
    Voting Booth?
    My first memorable Perl project was:

    Results (89 votes). Check out past polls.