tomatocans
asked on
Comvert Varchar to Date Time and Then Do Date Math
Have a table A with:
Field 1) local_departuretime (varchar(17)) ex MMDDYYYY_HH24MM or 12311999_1700
Field 2)gmt_departuretime(varcha r(17)) ex. MMDDYYYY_HH24MM or 12311999_0300
Field 3)local_arrivaltime(varcah r(17)) ex. MMDDYYYY_HH24MM or 12311999_1400
Field 4)gmt_arrivaltime(carchar( 17)) ex. MMDDYYYY_HH24MM or 12311999_0100
The MMDDYYYY stamp is based on the local departure time.
One
If the difference between the HH24MM (0300) of field 2 and HH24MM(1700) field 1 is greater than 1200, and it is not the last day of the month and is not the last day of the year, add 01 to the DD in field
2.
If the difference between the HH24MM (0300) of field 2 and HH24MM(1700) field 1 is greater than 1200, it is the last day of the month and is not the last day of the year, add 01 to both the MMand DD of field 2.
If the difference between the HH24MM (0300) of field 2 and HH24MM(1700) field 1 is greater than 1200, it is the last day of the year, add 01 to the MM, DD, and YYYY of field 2.
Two
If the HH24MM (0100) of field 4 is less than the HH24MM(0300) of field 2 and it is not the last day of
the month and it is not the last day of the year, add 01 to the DD in field 4.
If the HH24MM(0100) of field4 is less than the HH24MM(0300) of field 2 and it is the last day of the month and not the last day of the year add 01 to the MM and DD of field 4.
If the HH24MM(0100) of field4 is less than the HH24MM(0300) of field 2 and it is the last day of the year add 01 to the MM, DD, and YYYY of field 4.
Three
If the difference between the HH24MM (0100) of field 4 and HH24MM(1400) field 3 is greater than 1200, and it is not the first day of the month and is not the first day of the year, subtract 01 from the DD in field 3.
If the difference between the HH24MM (0100) of field 4 and HH24MM(1400) field 3 is greater than 1200, it is the first day of the month and is not the first day of the year, subtract 01 from both the MMand DD of field 3.
If the difference between the HH24MM (0100) of field 4 and HH24MM(1400) field 3 is greater than 1200, it is the first day of the year, subtract 01 from the MM, DD, and YYYY of field 3
The output for the examples would be
Field 1) local_departuretime (varchar(17)) ex MMDDYYYY_HH24MM or 12311999_1700
Field 2)gmt_departuretime(varcha r(17)) ex. MMDDYYYY_HH24MM or 01012000_0300
Field 3)local_arrivaltime(varcah r(17)) ex. MMDDYYYY_HH24MM or 01012000_1400
Field 4)gmt_arrivaltime(carchar( 17)) ex. MMDDYYYY_HH24MM or 01022000_0100
Is there a way to code this effectively with a procedure in Oracle 8i and if so
how would u code the procedure.
Thanks
Field 1) local_departuretime (varchar(17)) ex MMDDYYYY_HH24MM or 12311999_1700
Field 2)gmt_departuretime(varcha
Field 3)local_arrivaltime(varcah
Field 4)gmt_arrivaltime(carchar(
The MMDDYYYY stamp is based on the local departure time.
One
If the difference between the HH24MM (0300) of field 2 and HH24MM(1700) field 1 is greater than 1200, and it is not the last day of the month and is not the last day of the year, add 01 to the DD in field
2.
If the difference between the HH24MM (0300) of field 2 and HH24MM(1700) field 1 is greater than 1200, it is the last day of the month and is not the last day of the year, add 01 to both the MMand DD of field 2.
If the difference between the HH24MM (0300) of field 2 and HH24MM(1700) field 1 is greater than 1200, it is the last day of the year, add 01 to the MM, DD, and YYYY of field 2.
Two
If the HH24MM (0100) of field 4 is less than the HH24MM(0300) of field 2 and it is not the last day of
the month and it is not the last day of the year, add 01 to the DD in field 4.
If the HH24MM(0100) of field4 is less than the HH24MM(0300) of field 2 and it is the last day of the month and not the last day of the year add 01 to the MM and DD of field 4.
If the HH24MM(0100) of field4 is less than the HH24MM(0300) of field 2 and it is the last day of the year add 01 to the MM, DD, and YYYY of field 4.
Three
If the difference between the HH24MM (0100) of field 4 and HH24MM(1400) field 3 is greater than 1200, and it is not the first day of the month and is not the first day of the year, subtract 01 from the DD in field 3.
If the difference between the HH24MM (0100) of field 4 and HH24MM(1400) field 3 is greater than 1200, it is the first day of the month and is not the first day of the year, subtract 01 from both the MMand DD of field 3.
If the difference between the HH24MM (0100) of field 4 and HH24MM(1400) field 3 is greater than 1200, it is the first day of the year, subtract 01 from the MM, DD, and YYYY of field 3
The output for the examples would be
Field 1) local_departuretime (varchar(17)) ex MMDDYYYY_HH24MM or 12311999_1700
Field 2)gmt_departuretime(varcha
Field 3)local_arrivaltime(varcah
Field 4)gmt_arrivaltime(carchar(
Is there a way to code this effectively with a procedure in Oracle 8i and if so
how would u code the procedure.
Thanks
I meant your output example does NOT match your logic.
ASKER
Sorry about the error in logic. Will this also handle
the last day of the month and year scenarios.
Thanks dude.
the last day of the month and year scenarios.
Thanks dude.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The code has to be flexible enough to work with all dates not just 12/31
any help appreciated
any help appreciated
The code I gave handles last day of month and year. As I said, Oracle take care of it. Run queries below in SQL*PLUS, see what you get.
select to_date('12/31/1999','mm/d
from dual;
select to_date('11/30/1999','mm/d
from dual;
ASKER
how do u change your procedure to handle all dates from 01011999 to the present.
I am not quite sure about your question. Could you detail your requirement?
ASKER
Thanks
Hi, your example output does match your logic. Based on your logic, the output of Field 3 should be 12301999_1400, not 01012000_1400. The following codes follows your logic not your example. FYI, when you add 1 to a date type column, Oracle takes care of the end of month or year automatically. The query below gives you 20000101
select to_date('19991231','yyyymm
from dual;
procedure my_proc is
cursor a_cursor is
select rowid -- or your Primary key,
to_number(substr(local_dep
to_number(substr(gmt_depar
to_number(substr(local_arr
to_number(substr(gmt_arriv
from table_A;
begin
for a_record in a_cursor loop
-- This covers Case One
if (a_record.local_departuret
update table_A
set gmt_departuretime = to_char(to_date(gmt_depart
where rowid = a_record.rowid;
end if;
-- This covers Case Two
if a_record.gmt_arrivaltime < a_record.gmt_departuretime
update table_A
set gmt_arrivaltime = to_char(to_date(gmt_arriva
where rowid = a_record.rowid;
end if;
-- This covers Case Three
if (a_record.local_arrivaltim
update table_A
set local_arrivaltime = to_char(to_date(local_arri
where rowid = a_record.rowid;
end if;
end loop;
exception
when others then
-- handle your exception here
null;
end my_proc;