Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Re: DBIx::Simple and Oracle dates

by igelkott (Priest)
on Jun 15, 2010 at 00:01 UTC ( #844762=note: print w/replies, xml ) Need Help??

in reply to DBIx::Simple and Oracle dates

Could be a mismatch in Oracle's NLS_DATE_FORMAT. Could check/set this with DateTime::Format::Oracle.

If that's OK, should rewrite Option-1 to use a comparison since "like" isn't really appropriate here.

Replies are listed 'Best First'.
Re^2: DBIx::Simple and Oracle dates
by Argel (Prior) on Jun 15, 2010 at 00:06 UTC
    Will look into that tomorrow. Why isn't "like" appropriate? I'm only specifiy e.g. a date and leaving the time out, so it's not an exact match. BTW, why doesn't 'sysdate' work? Something preventing Oracle from running it through the DBI?

    Elda Taluta; Sarks Sark; Ark Arks

      Why isn't "like" appropriate?

      Because you can't just make stuff up? "LIKE" is a specific operator for matching strings where "%" can be used in one as a wildcard. It isn't some generic "roughly similar to" DWIM magical operator. Perhaps you should check your documentation?

      Now, I often regret when I have to learn more about Oracle (because it usually means I've had to try to use Oracle) and I know Oracle does some rather bizarre things. But I'd still be rather surprised to find out Oracle took the string-comparing LIKE and overloaded it for testing if datetimes were "near".

      - tye        

        As I indicated in my OP the query works in SQL Developer. Maybe it's massaging the code, or maybe newer clients support using "like" in that context. I actually did verify the code in SQL Developer, implying the problem could be a difference in the clients or with DBIx::Simple! You were on the right track (see the updated OP), but there was no need to be so hostile about it!

        Elda Taluta; Sarks Sark; Ark Arks

      Why isn't "like" appropriate?

      As has been said, a useful LIKE statement needs wildcards (otherwise it's just testing equality). Not to stray too far OT, but you'd also need to cast your date to a string (using to_char). Basically, my advice (for what it's worth) is to stick with comparisons like <= for dates.

      The part about the nls setting is more important. You should insure that your DBIx connection spits out dates in the same format as your other Oracle clients. Who knows, you could be trying to compare these to ISO 8601 format.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://844762]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (6)
As of 2021-04-11 19:24 GMT
Find Nodes?
    Voting Booth?

    No recent polls found