gwhite has asked for the wisdom of the Perl Monks concerning the following question:

I have a CGI application that runs on various servers and I am supporting various databases using DBI & DBD::(mySQL, ODBC, Oracle etc.) My biggest issue at this point is handling Oracle dates properly (or at least the way Oracle wants them), so far about the best solution is something like this in my database module:

    
if ( lc($self->{'config'}->db_driver) eq 'oracle' )
   {
   my $APrev = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:M
+I:SS'";
   $self->execute_sql($APrev);
   }

[download]

this gets Oracle into a "predictable" mode for handling dates. There is a built-in function call (todate) that I have yet to figure out how to access using DBD::Oracle. Does anyone have any better suggestions or knowledge of a Perl module that could parse or create SQL statements that handles these date variances properly?

g_White
Todays problems are yesterdays solutions.

Replies are listed 'Best First'.
Re: Oracle Dates DBD
by vek (Prior) on Aug 12, 2002 at 14:52 UTC
    There is a built-in function call (todate) that I have yet to figure out how to access using DBD::Oracle.

    Try this in your SQL: 
    UPDATE table SET dateColumn = TO_DATE ('2002-08-12', 'YYYY-MM-DD')

    [download]
    -- vek --
[reply]
[d/l]
Re: Oracle Dates DBD
by tommyw (Hermit) on Aug 12, 2002 at 14:50 UTC

    Strangely, that's the solution I always used ;-) Either that of set the date format to J, and you'll get all your dates back in Julian format (as numbers) which makes it really easy to add and subtract them. You just can't display them easily.

    For the other half of your comment, you've probably mixed up to_date and to_char: to_date converts from a string to a date value. to_char converts from a date to a string value.
    SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') FROM DUAL returns 12-AUG-2002 15:48:23

    --
    Tommy
    Too stupid to live.
    Too stubborn to die.

[reply]
[d/l]
Re: Oracle Dates DBD
by JupiterCrash (Monk) on Aug 12, 2002 at 18:19 UTC
    Dates are a pain, since every DBMS seems to handle them slightly differently. But for oracle, I generally like to use TO_DATE to insert dates into an Oracle table in the format you mentioned (ISO format) along with TO_CHAR.

    To insert a date, I would wrap it in TO_DATE, ie:

    INSERT into MYTABLE (MYBIRTHDAY) VALUES (TO_DATE('1977-07-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

    But then conversely, I like to just use TO_CHAR when querying a date, since it comes back in alpha-numberic order that way and I can simply do something like this:

    select * from MYTABLE WHERE TO_CHAR(MYBIRTHDAY,'YYYY-MM-DD') = '1977-07-09'

    Also, note that you can use the TO_CHAR function to wrap the date field in the section of the SQL which specifies the fields to select. ( where I have '*', you could have TO_CHAR(MYBIRTHDAY, 'YYYY-MM-DD'), OTHERFIELD1, OTHERFIELD2 )
[reply]
Re: Oracle Dates DBD
by Ryszard (Priest) on Aug 12, 2002 at 15:00 UTC
    to_date is just a function. you use it like this:

  • select to_date('26-mar-2002', 'dd-mon-yyyy') from dual;
  • select count(*) from dual where to_date('26-mar-2002','dd-mon-yyyy') < sysdate;

    If you want to use it in your select then you have to use variables, however if you want to use it in your where you can use place holders. Place holders dont work in selects.

    So, you can do this select count(*) from dual where todate (?, 'dd-mon-yyyy') < sysdate

    Update: Removed a completely incorrect statement. thx tommyw.

[reply]
[d/l]
[select]

      Eh? Placeholders do work in selects. That last example will take a string (in dd-mon-yyyy format), convert and convert it into a date. The database will then return the result of this, but as a string, using the default database format.

      So if NLS_DATE_FORMAT has been set as in the previous messages, then select to_date(?, 'dd-mon-yyyy') from dual; will convert a parameter of '12-aug-2002' into '2002-08-12 00:00:00' (since there's no time component, it'll use midnight, and return all zeros)

      --
      Tommy
      Too stupid to live.
      Too stubborn to die.

[reply]
[d/l]
        Whoops, didnt test my answer, just remember something about something......

        So therefore can i do something like: select ?, ?, ? from dual (not near a computer to test right now)

[reply]

Back to Seekers of Perl Wisdom