[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How Do I write an if statement between times

Posted on 2007-12-05
29
Medium Priority
?
1,172 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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20409955
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
ID: 20409968
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
ID: 20410017
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 12

Expert Comment

by:jwahl
ID: 20410060
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
ID: 20410072
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
ID: 20410081

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
ID: 20410110
@ 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
ID: 20410169

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
ID: 20410179
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
ID: 20410183
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
ID: 20410215
@ sonicefu - Correct! Noted :)
0
 

Author Comment

by:anawobas
ID: 20417567
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
ID: 20417895
please post the code where you get the error.
0
 

Author Comment

by:anawobas
ID: 20417940
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20417956
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
ID: 20418010
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
ID: 20418214
angelIII:
sonicefu:

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20418249
>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
ID: 20418292
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
ID: 20418397
sonicefu: the data type is DATE



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

Expert Comment

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20418749
>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
ID: 20425648
>> 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
ID: 20436426
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
ID: 20436458
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
ID: 20436483
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
ID: 20436495
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 2000 total points
ID: 20436872
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
ID: 20439683
jwahl:

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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month18 days, 19 hours left to enroll

834 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