Date-Time conversion 1

[RottPaws]

I'm trying to create a report of information in a scheduling database. The scheduling information is divided into 2 fields, one for the date and another for the time.

The START_DATE field is a DATE datatype. The START_TIME is VARCHAR2 with times entered as 0, 30, 100, 130, ... 2330.

To further complicate things, some times are entered as Eastern and others as GMT.

I need to be able to standardize the times for comparison in the report.

I started with trying to combine the date and time fields into a date.

This query will output the date/time in the desired format (i.e. 200603260400):

select
    r.*,
    to_char(start_date, 'YYYYMMDD') || lpad(start_time,4,'0') SDT
 from 
    reservations r

But when I try to convert it to a date for comparison, I get an error:

select
    start_date,
    start_time,
    customer_name
from
    (select
        r.*,
        to_char(start_date, 'YYYYMMDD') || lpad(start_time,4,'0') SDT
     from 
        reservations r) res
where
    to_date(res.sdt, 'YYYYMMDDHH24MI') > sysdate

    to_date(res.sdt, 'YYYYMMDDHH24MI') > sysdate
            *

ERROR at line 12: 
ORA-01850: hour must be between 0 and 23

How can I do what I need to do?

And once I get that part working, I'll need be able to add or subtract 5 hours from a date (4 durning daylight savings).

That's (DATE_FIELD - (5/24)), right?



_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[SantaMufasa]

RottPaws,

First, all Oracle DATE datatypes contain date and time components. Is there a good reason that your application is puposely making life difficult by separating the components artificially?

Second, I cannot see anything obviously causing your above error. Could you please post the results of the following query against your data:

select min(to_number(start_time))min,max(to_number(start_time))max
from reservations;

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[RottPaws]

I presume the date and time were split for the same reason that the decision was made to store records in 2 different time zones in the same table, but what that reason might be, I have no idea.

The query you asked me to run returns 0 and 2500. I'm told 2500 is used to identify appointments that are scheduled for a certain day at a non-specified time. I didn't design the database. I just have to use it . . .

For my purposes, the values will be 0 - 2330. I thought, for a moment, that you had provided me the answer, but I added "where start_time < 2400" in the sub-query and I still get the same results.




_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[SantaMufasa]

Perhaps I might have provided the answer, Rott...Try this and see what happens:

select
    start_date,
    start_time,
    customer_name
from
    (select
        r.*,
        to_char(start_date, 'YYYYMMDD') || lpad(start_time,4,'0') SDT
     from 
        reservations r[b]
     where to_number(start_time) < 2400)[/b] res
where
    to_date(res.sdt, 'YYYYMMDDHH24MI') > sysdate

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[RottPaws]

That worked, but I'm not sure why. I was mistaken in my original post. The START_TIME field is NUMBER(10).

I now have a query working that converts the GMT reservations to Eastern.

select
    to_char(res.SDT, 'MM/DD/YYYY') START_DATE,
    to_char(res.sdt, 'HH24MI') start_time,
    customer_name
from
    (select
        r.*,
        (to_date(to_char(start_date, 'YYYYMMDD') || lpad(start_time,4,'0'), 'YYYYMMDDHH24MI') - (5/24)) SDT
     from
        ADMIN_OSSPROD.FILTERED_RSVPS_DEPT_8 r
     where to_number(start_time) < 2400) res
where
    res.sdt > sysdate
order by
    res.sdt

Thank you very much! As always, you've been a big help.

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!