in reply to Oracle Dates DBD

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.

    • Replies are listed 'Best First'.
    Re: Re: Oracle Dates DBD
    by tommyw (Hermit) on Aug 12, 2002 at 15:13 UTC

      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]

          Yes, but it won't be very useful ;-)

          More likely: select function(?), other_function(?), third_function(?) from dual which will return whatever mangling Oracle needs to do to the data

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

    		[reply]
    [d/l]

    In Section Seekers of Perl Wisdom