Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Comvert Varchar to Date Time and Then Do Date Math

Posted on 2000-05-15
9
Medium Priority
?
509 Views
Last Modified: 2011-10-03
Have a table A with:

Field 1) local_departuretime (varchar(17)) ex  MMDDYYYY_HH24MM or  12311999_1700
Field 2)gmt_departuretime(varchar(17)) ex. MMDDYYYY_HH24MM or 12311999_0300
Field 3)local_arrivaltime(varcahr(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(varchar(17)) ex. MMDDYYYY_HH24MM or 01012000_0300
Field 3)local_arrivaltime(varcahr(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
0
Comment
Question by:tomatocans
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 1

Expert Comment

by:jyhuang
ID: 2812230

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','yyyymmdd') + 1
from dual;

procedure my_proc is
cursor a_cursor is
  select rowid -- or your Primary key,
         to_number(substr(local_departuretime,10,4)) local_departuretime,
         to_number(substr(gmt_departuretime, 10,4)) gmt_departuretime,
         to_number(substr(local_arrivaltime, 10,4)) local_arrivaltime,
         to_number(substr(gmt_arrivaltime, 10,4)) gmt_arrivaltime
  from table_A;
begin
  for a_record in a_cursor loop
      -- This covers Case One
      if (a_record.local_departuretime - a_record.gmt_departuretime) > 1200 then
         update table_A
         set gmt_departuretime = to_char(to_date(gmt_departuretime,'MMDDYYYY_HH24MI') + 1, 'MMDDYYYY_HH24MI')
         where rowid = a_record.rowid;
      end if;

      -- This covers Case Two
      if a_record.gmt_arrivaltime < a_record.gmt_departuretime then
         update table_A
         set gmt_arrivaltime = to_char(to_date(gmt_arrivaltime,'MMDDYYYY_HH24MI') + 1, 'MMDDYYYY_HH24MI')
         where rowid = a_record.rowid;
      end if;
 
      -- This covers Case Three
      if (a_record.local_arrivaltime - a_record.gmt_arrivaltime) > 12 then
         update table_A
         set local_arrivaltime = to_char(to_date(local_arrivaltime,'MMDDYYYY_HH24MI') - 1, 'MMDDYYYY_HH24MI')
         where rowid = a_record.rowid;
      end if;
  end loop;
exception
  when others then
    -- handle your exception here
    null;
end my_proc;
0
 
LVL 1

Expert Comment

by:jyhuang
ID: 2812233

I meant your output example does NOT match your logic.
0
 

Author Comment

by:tomatocans
ID: 2812324
Sorry about the error in logic. Will this also handle
the last day of the month and year scenarios.

Thanks dude.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 9

Accepted Solution

by:
konektor earned 400 total points
ID: 2814551
oracle has quite good functions to manipulate with date and time

select sysdate from dual;
16.5.2000 14:31:25

1.
select sysdate+1 from dual;
17.5.2000 14:31:25
2.
select sysdate-1 from dual;
15.5.2000 14:31:25
3.
select add_months(sysdate,-1) from dual;
16.4.2000 14:31:25
4.
select last_day(sysdate) from dual;
31.5.2000 14:31:25
5.
select trunc(sysdate,'mi') from dual;
16.5.2000 14:31:00
6.
select trunc(sysdate,'hh') from dual;
16.5.2000 14:00:00
7.
select trunc(sysdate,'dd') from dual;
16.5.2000 00:00:00
8.
select trunc(sysdate,'mm') from dual;
1.5.2000 00:00:00
9.
select trunc(sysdate,'yyyy') from dual;
1.1.2000 00:00:00

....

0
 

Author Comment

by:tomatocans
ID: 2814651
The code has to be flexible enough to work with all dates not just 12/31

any help appreciated
0
 
LVL 1

Expert Comment

by:jyhuang
ID: 2814871

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/dd/yyyy') + 1
from dual;

select to_date('11/30/1999','mm/dd/yyyy') + 1
from dual;

0
 

Author Comment

by:tomatocans
ID: 2814933
how do u change your procedure to handle all dates from 01011999 to the present.
0
 
LVL 1

Expert Comment

by:jyhuang
ID: 2817184

I am not quite sure about your question. Could you detail your requirement?
0
 

Author Comment

by:tomatocans
ID: 2830672
Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question