Link to home
Start Free TrialLog in
Avatar of anawobas
anawobas

asked on

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

in addition to the date condition, you add:

AND TO_CHAR(yourfield, 'HHMM') >= '0630'
AND TO_CHAR(yourfield, 'HHMM') <= '0830'
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;
Avatar of anawobas
anawobas

ASKER

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;

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

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.

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

@ 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;

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

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...........
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.
@ sonicefu - Correct! Noted :)
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 ???
please post the code where you get the error.
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;
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...


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

angelIII:
sonicefu:

it doesn't work , It saves the date only ...
>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...


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
sonicefu: the data type is DATE



angelIII: I Can SEE that through the TOAD
One way to store/retrieve complete information about datetime is to use TIMESTAMP data type in your table for the field.
>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?
>> 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?
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
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.
 
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.


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;
ASKER CERTIFIED SOLUTION
Avatar of jwahl
jwahl
Flag of Austria image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
jwahl:

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