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
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
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;
IF TO_CHAR(date_field,'HH24MM
INSERT INTO table_name(date_field) VALUES(SYSDATE);
END IF;
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_TYP E = 1: means on time.
3) EMP_TRANSACTIONS.TRANS_TYP E = 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_TY PE := 1;
:EMP_TRANSACTIONS.TRANS_DA TE := 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;
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_TYP
3) EMP_TRANSACTIONS.TRANS_TYP
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
:EMP_TRANSACTIONS.TRANS_TY
:EMP_TRANSACTIONS.TRANS_DA
: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' );
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;
@ 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.flexibl e = 1) THEN
:EMP_TRANSACTIONS.TRANS_TY PE := 1;
:EMP_TRANSACTIONS.TRANS_DA TE := 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_NUMBER(TO_CHAR(SYSDATE,
AND (:emp_transactions.flexibl
:EMP_TRANSACTIONS.TRANS_TY
:EMP_TRANSACTIONS.TRANS_DA
: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;
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...........
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.
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 :)
ASKER
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 ???
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.
ASKER
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)
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...
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;
ASKER
angelIII:
sonicefu:
it doesn't work , It saves the date only ...
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...
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
i wrote this code assuming that data type is DATE. If you want to store complete time stamp than use TIMESTAMP
ASKER
sonicefu: the data type is DATE
angelIII: I Can SEE that through the TOAD
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?
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?
Do u mean to say it saves the values for :BLOCK.SYSTEM_DATE and :BLOCK.LEAVE_DATE, but not for the :BLOCK.TRANS_TYPE?
ASKER
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
jinesh_kamdar: it saves everything but the :BLOCK.SYSTEM_DATE and :BLOCK.LEAVE_DATE it saves the date only without the time
ASKER
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.
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.
"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.
ASKER
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_DA TE , '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_DA TE , 'DD\MM\YYYY HH24:MI:SS') + (7 / 24), 'DD\MM\YYYY HH24:MI:SS');
end if;
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_DA
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_DA
end if;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
jwahl:
I did it ............ Thank you all :)
I did it ............ Thank you all :)
AND TO_CHAR(yourfield, 'HHMM') >= '0630'
AND TO_CHAR(yourfield, 'HHMM') <= '0830'