Solved

How Do I write an if statement between times

Posted on 2007-12-05
29
1,159 Views
Last Modified: 2013-12-07
i want to insert the sysdate after checking if the time between 6:30 and 8:30
the format is DD-MON-YYYY HH:MI:SS, but i need the if statement only between the time not the whole date eg. if SYSTEM_DATE BETWEEN 0630 AND 0830
0
Comment
Question by:anawobas
  • 9
  • 6
  • 5
  • +3
29 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
in addition to the date condition, you add:

AND TO_CHAR(yourfield, 'HHMM') >= '0630'
AND TO_CHAR(yourfield, 'HHMM') <= '0830'
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
Comment Utility
Something like this : -

IF TO_CHAR(date_field,'HH24MM') >= '0630' AND TO_CHAR(date_field,'HH24MM') <= '0830' THEN
    INSERT INTO table_name(date_field) VALUES(SYSDATE);
END IF;
0
 

Author Comment

by:anawobas
Comment Utility
Actually I need an insert statement that adds 7 hours to the time that the employee punch the card in.

For example: I punch my card at 8:30 so the system should add 7 hours to the time so my leave time will be 8:30 am + 7 hours = 3:30 pm

I wrote this code & if any one can help

 

Notes:

1)       Flexible time: the employee can come any time between 6:30 and 8:30.

2)       EMP_TRANSACTIONS.TRANS_TYPE =  1: means on time.

3)       EMP_TRANSACTIONS.TRANS_TYPE = 2: means late.

4)       flexible = 1: using the flexible time

5)       flexible = 0: doesn't use the flexible time.

if TO_CHAR(:SYSTEM_DATE, 'HH24MI') >= '0630' AND TO_CHAR(:SYSTEM_DATE, 'HH24MI') <= '0830' and :EMP_TRANSACTIONS.flexible = 1 then

:EMP_TRANSACTIONS.TRANS_TYPE := 1;
:EMP_TRANSACTIONS.TRANS_DATE := TO_CHAR(SYSDATE ,'DD-MON-YYYY HH:MI:SS' );    
:TRANS_DATE.LEAVE_DATE := TO_CHAR(:SYSTEM_DATE + 7 ,'DD-MON-YYYY HH:MI:SS' );      
 
end if;

0
 
LVL 12

Expert Comment

by:jwahl
Comment Utility
to add 7 hours, use
:TRANS_DATE.LEAVE_DATE := TO_CHAR(:SYSTEM_DATE + 7/24 ,'DD-MON-YYYY HH:MI:SS' );  

Open in new window

0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
Comment Utility
I assume this code is being written for a unit within an Oracle Form. Is :SYSTEM_DATE a parameter variable? If it is and if its date-type, then use 7/24 as the addition factor.
0
 
LVL 13

Expert Comment

by:sonicefu
Comment Utility

IF     (TO_CHAR (SYSDATE, 'HH24MI') BETWEEN 630 AND 830)

      AND (:emp_transactions.flexible = 1)

   THEN

   /*Write Your code here*/

END IF;

Open in new window

0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
Comment Utility
@ sonicefu - I guess u missed the TO_NUMBER() call. Just correcting it :)

IF TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI') BETWEEN 630 AND 830)
               AND (:emp_transactions.flexible = 1) THEN
    :EMP_TRANSACTIONS.TRANS_TYPE := 1;
    :EMP_TRANSACTIONS.TRANS_DATE := TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS' );    
     :TRANS_DATE.LEAVE_DATE := TO_CHAR(:SYSTEM_DATE + 7/24, 'DD-MON-YYYY HH:MI:SS' );
END IF;
0
 
LVL 13

Expert Comment

by:sonicefu
Comment Utility

IF     (TO_CHAR (:system_date, 'HH24MI') BETWEEN 630 AND 830)

      AND (:emp_transactions.flexible = 1)

   THEN

      :emp_transactions.trans_type := 1;

      :emp_transactions.trans_date := TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS');

      :trans_date.leave_date := TO_CHAR (:system_date + 7 / 24, 'DD-MON-YYYY HH:MI:SS');

   END IF;

Open in new window

0
 
LVL 13

Expert Comment

by:sonicefu
Comment Utility
Hi !  jinesh_kamdar
You wrote this comment
@ sonicefu - I guess u missed the TO_NUMBER() call. Just correcting it :)

BUT there is no need for conversion, it will done impliciltly...........
0
 
LVL 3

Expert Comment

by:prasanthi_k
Comment Utility
Hi,

In the PL/SQL code, the employee punch time cannot be SYSDATE. (I guess the PL/SQL code can be run during any time of the day)

The punch in time of the employees should be saved in some Date column (say for eg: emp_in_time) of some table, and that column name should be used in the IF condition as written in the above comments posted.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
Comment Utility
@ sonicefu - Correct! Noted :)
0
 

Author Comment

by:anawobas
Comment Utility
Thank You all i wrote the code, but i still have problems ... NOW i got this error:

Error:
 ORA-01830: date format picture ends before converting entire input string
Cause:
 You tried to enter a date value, but the date entered did not match the date format.

can any body help ???
0
 
LVL 12

Expert Comment

by:jwahl
Comment Utility
please post the code where you get the error.
0
 

Author Comment

by:anawobas
Comment Utility
this is the code:

IF     (TO_CHAR (:SYSTEM_DATE, 'HH24MI') BETWEEN 630 AND 830)
   
   THEN
      :BLOCK2.trans_type := 1;
      :BLOCK2.SYSTEM_DATE := TO_CHAR (SYSDATE, 'DD/MM/YYYY hh:mi:ss');
      :BLOCK2.leave_date := TO_CHAR (:system_date + 7 / 24, 'DD/MM/YYYY hh:mi:ss' );

            
      else
            :BLOCK2.TRANS_TYPE := 2;
      
      :BLOCK2.SYSTEM_DATE := TO_CHAR (SYSDATE, 'DD/MM/YYYY hh:mi:ss');
      :BLOCK2.leave_date := TO_CHAR (:system_date + 7 / 24, 'DD/MM/YYYY hh:mi:ss');
            
      end if;
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
IF     (TO_CHAR (:SYSTEM_DATE, 'HH24MI') BETWEEN 630 AND 830)
should be
IF     (TO_CHAR (:SYSTEM_DATE, 'HH24MI') BETWEEN '0630' AND '0830')


now:
      :BLOCK2.SYSTEM_DATE := TO_CHAR (SYSDATE, 'DD/MM/YYYY hh:mi:ss');
      :BLOCK2.leave_date := TO_CHAR (:system_date + 7 / 24, 'DD/MM/YYYY hh:mi:ss' );

I have to assume that the 2 fields/variables are dates? then, remove the TO_CHAR() altogether...


0
 
LVL 13

Expert Comment

by:sonicefu
Comment Utility
http://www.techonthenet.com/oracle/errors/ora01830.php
IF     (TO_CHAR (:SYSTEM_DATE, 'HH24MI') BETWEEN 630 AND 830)

   

   THEN

      :BLOCK2.trans_type := 1;

      :BLOCK2.SYSTEM_DATE := to_date(TO_CHAR (SYSDATE, 'DD/MM/YYYY hh:mi:ss'),'DD/MM/YYYY hh:mi:ss');

      :BLOCK2.leave_date := to_date(TO_CHAR (:system_date + 7 / 24, 'DD/MM/YYYY hh:mi:ss' ),'DD/MM/YYYY hh:mi:ss');
 

            

      else

            :BLOCK2.TRANS_TYPE := 2;

      

      :BLOCK2.SYSTEM_DATE :=to_date(TO_CHAR (SYSDATE, 'DD/MM/YYYY hh:mi:ss'),'DD/MM/YYYY hh:mi:ss');

      :BLOCK2.leave_date := to_date(TO_CHAR (:system_date + 7 / 24, 'DD/MM/YYYY hh:mi:ss'),'DD/MM/YYYY hh:mi:ss');

            

      end if;

Open in new window

0
 

Author Comment

by:anawobas
Comment Utility
angelIII:
sonicefu:

it doesn't work , It saves the date only ...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>it doesn't work , It saves the date only ...
how do you "see" that?

if you query a date (datetime) field, you might either configure the nls_ parameters concerning datetime, or during the query use to_char():

select to_char(yourfield, 'YYYY-MM-DD HH24:MI:SS') as yourfield from yourtable

that should NOT contain 00:00:00 in the time part with the above queries...


0
 
LVL 13

Expert Comment

by:sonicefu
Comment Utility
Please let me know what is data type you are using to store date information.
i wrote this code assuming that data type is DATE. If you want to store complete time stamp than use TIMESTAMP
0
 

Author Comment

by:anawobas
Comment Utility
sonicefu: the data type is DATE



angelIII: I Can SEE that through the TOAD
0
 
LVL 13

Expert Comment

by:sonicefu
Comment Utility
One way to store/retrieve complete information about datetime is to use TIMESTAMP data type in your table for the field.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>angelIII: I Can SEE that through the TOAD
how exactly? running a query? looking at the table data in the schema browser?
what are your nls_ parameters settings?
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
Comment Utility
>> it doesn't work , It saves the date only ...
Do u mean to say it saves the values for :BLOCK.SYSTEM_DATE and :BLOCK.LEAVE_DATE, but not for the :BLOCK.TRANS_TYPE?
0
 

Author Comment

by:anawobas
Comment Utility
angelIII: when i see the table's data i see only the date and when i run the query i got the date with 00:00:00 for the timing.

jinesh_kamdar: it saves everything but the :BLOCK.SYSTEM_DATE and :BLOCK.LEAVE_DATE it saves the date only without the time
0
 

Author Comment

by:anawobas
Comment Utility
And stiil I'm having this error:
Error:
 ORA-01830: date format picture ends before converting entire input string
 
Cause:
 You tried to enter a date value, but the date entered did not match the date format.
 
0
 
LVL 12

Expert Comment

by:jwahl
Comment Utility
i'm confused - you say:
"it saves everything but the :BLOCK.SYSTEM_DATE and :BLOCK.LEAVE_DATE it saves the date only without the time"
but also
"And stiil I'm having this error ..."

so if you're getting this error, your update ist NOT saved and you are NOT able to check if the time is saved correctly.

1)
do you work with forms? if so, please check the type of the fields.

2)
you've posted some code above, but in the meantime you surely did some changes. please post the current code that raises the error above.


0
 

Author Comment

by:anawobas
Comment Utility
You are right , I'm getting the errors because of some changes. so this is the last updated code:


IF     (to_char (to_date(:SYSTEM_DATE, 'HH24:MI:SS'), 'HH24MISS') BETWEEN '0630' AND '0830')

   THEN
      :BLOCK2.trans_type := 1;
      :BLOCK2.SYSTEM_DATE :=  to_char(sysdate, 'DD\MM\YYYY HH24:MI:SS');
      :BLOCK2.leave_date :=   to_char(to_date(:SYSTEM_DATE ,  'DD\MM\YYYY HH24:MI:SS') + (7 / 24),  'DD\MM\YYYY HH24:MI:SS');

   else
          :BLOCK2.TRANS_TYPE := 2;
          :BLOCK2.SYSTEM_DATE :=  to_char(sysdate, 'DD\MM\YYYY HH24:MI:SS');
      :BLOCK2.leave_date :=   to_char(to_date(:SYSTEM_DATE ,  'DD\MM\YYYY HH24:MI:SS') + (7 / 24),  'DD\MM\YYYY HH24:MI:SS');
            
   end if;
0
 
LVL 12

Accepted Solution

by:
jwahl earned 500 total points
Comment Utility
check this: --> IF  (to_char (to_date(:SYSTEM_DATE, 'HH24:MI:SS'), 'HH24MISS') BETWEEN '0630' AND '0830')

:BLOCK2.SYSTEM_DATE seems to be CHAR, right?

because you always store date/time in format  'DD\MM\YYYY HH24:MI:SS', you have to convert it first in the same format and THEN retrieve hours/minutes with TO_CHAR like this:

IF TO_CHAR (TO_DATE (:BLOCK2.SYSTEM_DATE, 'DD\MM\YYYY HH24:MI:SS'), 'HH24MI') BETWEEN '0630' AND '0830' THEN
...

btw:

1)
don't use 'HH24MISS', because you only need hours and minutes for the IF compare.

2)
what's the difference between IF and ELSE block (except assigning another :BLOCK2.trans_type)?
0
 

Author Comment

by:anawobas
Comment Utility
jwahl:

I did it ............ Thank you all :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
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.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now