2 Replies Latest reply: May 7, 2013 2:08 PM by Howard (... in Training) RSS

    Date format

      when I enter the data from the form I use the calendar which gives me the date and time.
      but in all my reports its only showing the date with out time.another thing I would like to use "between" in my clause this way :

      where between to_date ('2013/04/30', 'yyyy/mm/dd')
      AND to_date ('2013/05/02', 'yyyy/mm/dd')

      How can I add the time inside this query?
        • 1. Re: Date format
          Hi Beena-IT,

          You should add the format to the column in your report
          Look here -> http://www.techonthenet.com/oracle/functions/to_date.php for a good description of to_date and the date/time formats.

          Kees Vlek
          Company: http://www.orcado.nl
          Blog: http://www.orcado.nl/blog/blogger/listings/69-kvlek
          Twitter: http://www.twitter.com/skier66
          If the question is answered please change it to answered and mark the appropriate post as correct/helpfull.
          • 2. Re: Date format
            Howard (... in Training)

            where between to_date ('2013/04/30', 'yyyy/mm/dd')
            AND to_date ('2013/05/02', 'yyyy/mm/dd')
            Time variables (example, 10:45 AM) are similar to non-integral numbers (example, 3.17) in that a test for "<variable>=3" possibly gets 3, 3., 3.0, 3.00, etc. but not 3.17. So if you are able to get a date picker to give you hours, minutes and seconds, perhaps you want something like:
            where :given_date between to_date(:BEGIN_DATE, 'yyyy/mm/dd hh24:mi:ss') 
                                  AND to_date(:END_DATE,   'yyyy/mm/dd hh24:mi:ss')
            [Here I assume you have date pickers for a *beginning date* and an *ending date*.]

            If only hours and minutes, then:
            where :given_date between to_date(:BEGIN_DATE, 'yyyy/mm/dd hh24:mi') 
                                  AND to_date(:END_DATE,   'yyyy/mm/dd hh24:mi')
            If only days, then perhaps:
            where :given_date between to_date(:BEGIN_DATE, 'yyyy/mm/dd') 
                                  AND to_date(:END_DATE || 23:59:59', 'yyyy/mm/dd hh24:mi')
            For this last one, you only get the first second of the ending date/day unless you add the rest of the day ('23:59"59') to the comparison.

            Hope this helps some,