#1
  1. Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Los Angeles
    Posts
    2
    Rep Power
    0

    Angry Time/Date Ranges in Oracle 8.x


    Perhaps one of you out there can help...
    I am trying to create a report in Oracle based on time and date ranges. The date range works perfectly but I am running into issues with the time range. I am thinking that the query does not know to associate the time with the date.

    The table has two separate fields for time and date. Neither of which were designed to use the a database date/time format, so this report has been tedious to write.

    I am programming the report application in ColdFusion MX, fyi.

    Here is my query:

    SELECT *
    FROM table_1
    WHERE to_date(date1,'YYYYMMDD') between to_date('20031201','YYYYMMDD') and to_date('20031202','YYYYMMDD')
    AND to_date(lpad(time_field,6,'0'),'HH24MISS') between to_date('000000','HH24MISS') and to_date('070000','HH24MISS')
    ORDER BY internet_creation_date desc, internet_creation_time desc


    I was thinking that my solution might be found in subqueries under the WHERE clause.

    Any help would be appreciated.

    breklin
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2002
    Posts
    693
    Rep Power
    26
    I think this should work
    PHP Code:
    SELECT *
      
    FROM table_1
      WHERE to_date
    (date_field||time_field,'YYYYMMDDHH24MISS'between 
            to_date
    ('20031201000000','YYYYMMDDHH24MISS') and 
            
    to_date('20031201070000','YYYYMMDDHH24MISS')
    ORDER BY internet_creation_date descinternet_creation_time desc 
    it just concatenates the date and time togther to get a date field, then the comparison is easier.
  4. #3
  5. Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Los Angeles
    Posts
    2
    Rep Power
    0

    Cool. Thanks.


    don't know why i didn't think of that. so simple. anyhow thanks. it's solved my issue.

IMN logo majestic logo threadwatch logo seochat tools logo