DBI Date Formatting

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

Comment on DBI Date Formatting

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;` [download] To let Oracle do the formatting. pelagic	[reply] [d/l]
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.	[reply] [d/l]
Re^2: DBI Date Formatting by Anonymous Monk on Jan 14, 2016 at 15:37 UTC
You saved my life. :) alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'	[reply]
Re^2: DBI Date Formatting by Anonymous Monk on Mar 23, 2016 at 17:18 UTC
Thank you. Thank you. Thank you.	[reply]
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	[reply]
Re: DBI Date Formatting by elwarren (Priest) 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` [download] rr date handling and oracle date formatting. HTH	[reply] [d/l]