Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1174
  • Last Modified:

How Do I write an if statement between times

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
anawobas
Asked:
anawobas
  • 9
  • 6
  • 5
  • +3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in addition to the date condition, you add:

AND TO_CHAR(yourfield, 'HHMM') >= '0630'
AND TO_CHAR(yourfield, 'HHMM') <= '0830'
0
 
Jinesh KamdarCommented:
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
 
anawobasAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
jwahlCommented:
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
 
Jinesh KamdarCommented:
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
 
sonicefuCommented:

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
 
Jinesh KamdarCommented:
@ 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
 
sonicefuCommented:

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
 
sonicefuCommented:
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
 
prasanthi_kCommented:
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
 
Jinesh KamdarCommented:
@ sonicefu - Correct! Noted :)
0
 
anawobasAuthor Commented:
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
 
jwahlCommented:
please post the code where you get the error.
0
 
anawobasAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
sonicefuCommented:
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
 
anawobasAuthor Commented:
angelIII:
sonicefu:

it doesn't work , It saves the date only ...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
sonicefuCommented:
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
 
anawobasAuthor Commented:
sonicefu: the data type is DATE



angelIII: I Can SEE that through the TOAD
0
 
sonicefuCommented:
One way to store/retrieve complete information about datetime is to use TIMESTAMP data type in your table for the field.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
Jinesh KamdarCommented:
>> 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
 
anawobasAuthor Commented:
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
 
anawobasAuthor Commented:
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
 
jwahlCommented:
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
 
anawobasAuthor Commented:
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
 
jwahlCommented:
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
 
anawobasAuthor Commented:
jwahl:

I did it ............ Thank you all :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 9
  • 6
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now