This discussion is archived
2 Replies Latest reply: May 7, 2013 12:08 PM by Howard (... in Training) RSS

Date format

prog Newbie
Currently Being Moderated
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
    kvlek Journeyer
    Currently Being Moderated
    Hi Beena-IT,

    You should add the format to the column in your report
    Look here -> for a good description of to_date and the date/time formats.

    Kees Vlek
    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) Pro
    Currently Being Moderated

    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,


  • Correct Answers - 10 points
  • Helpful Answers - 5 points