| date and time problem [message #87733] |
Sat, 05 February 2005 06:00  |
swapna
Messages: 11
Registered: May 2002
|
Junior Member |
|
|
i have hit with a date and time problem
kindly help me
let me put it in a simple example
i have created a form with 2 blocks , one is the empblock and the
other is some temptime table which has only one column ttime defined
as varchar2(10);
in the temptime table i have only one record and the value is '04:30'
all the records in the emp table are having time stamp as 00:00:00
when the emp block is queried it displays the hiredate column value in
dd-mon-yyyy
now instead of this i want to display the temptime table value i.e,
instead of '17-dec-80' i want to display 04:30.
how do i achieve this
i have written this peice of code in post-query but this is not working for me
message('date is :'||:emp.hiredate);---> this message properly
displays 17-dec-80
:emp.hiredate := to_date(:temptime.ttime,'hh24:mi');
message('date is :'||:emp.hiredate); --> after assignment this is
displaying null
message('date conv is :'||to_char (:emp.hiredate,'hh24:mi'));
kindly suggest a solution for this.
|
|
|
|
| Re: date and time problem [message #87735 is a reply to message #87733] |
Sat, 05 February 2005 09:02  |
SANDY
Messages: 60
Registered: April 1998
|
Member |
|
|
Hi Swapna,
U plz use to_char(:temptime.ttime,'hh24:mi'); instead of to_date(:temptime.ttime,'hh24:mi'). perhaps it will solve ur problem. If not, then reply me again.
cheers
sandy
|
|
|
|
| Re: date and time problem [message #87740 is a reply to message #87735] |
Sun, 06 February 2005 19:45 |
Swapna C
Messages: 6
Registered: February 2005
|
Junior Member |
|
|
Hi Sandy,
I cannot use to_char as the emp.hiredate is a date field.
The properties of this field are defined as
textitem and datatype date.
so if i specify to_char, the form will fail to compile
regards
swapna
|
|
|
|
| Re: date and time problem [message #87745 is a reply to message #87740] |
Sun, 06 February 2005 21:45 |
fanni
Messages: 96
Registered: March 2004
|
Member |
|
|
you receive null as you have specified the date format mask for :emp.datefiels as 'DD/MM/YYYY' and are assigning 'HH24:MI' data in it. remove the format masking from the :emp.datefield and then try your assignment.
farhan
|
|
|
|
| Re: date and time problem [message #87746 is a reply to message #87745] |
Sun, 06 February 2005 22:53 |
Swapna C
Messages: 6
Registered: February 2005
|
Junior Member |
|
|
Hi,
I have even tried setting the format in propererty pallete but the result is same
i tried the following in when new form instance
set_item_property('emp.hire_Date',format_mask,'HH24:MI');
still the result is same
regards
swapna
|
|
|
|
| Re: date and time problem [message #87747 is a reply to message #87746] |
Sun, 06 February 2005 23:15 |
fanni
Messages: 96
Registered: March 2004
|
Member |
|
|
Dear swapna
infact yr problem statement is not very clear,,,can y plz write it more briefly and also desc the table structres
as it should not be so big deal as it is posing problem.
regards
Farhan
|
|
|
|
| Re: date and time problem [message #87748 is a reply to message #87747] |
Mon, 07 February 2005 00:01 |
Swapna C
Messages: 6
Registered: February 2005
|
Junior Member |
|
|
I have got a problem with showing only the time stamp in a date filed.
Instead of using my proj related tables and fields im trying to convey my problem using the emp table of scott/tiger schema
the other table temptime is having only column ttime and it is defined as varchar2(10) and that table is having one row with value '06:30'
i have built a form based on the emp table and in the post-query i have written the following statements.
Declare
X_Ttime varchar2(10);
Begin
Select to_char(to_date(ttime,'HH24:MI'),'HH24:MI') INTO x_ttime FROM temptime;
message('hire date is :'||:emp.hiredate); --- this displays 17-Dec-80
message('temp date is :'||x_ttime); --- this displays 6:30
:emp.hiredate := to_date(x_ttime,'HH24:MI');
message('hire date is :'||:emp.hiredate); --this displays 01-FEB-05
message ('Hire Date is:'||to_char(:emp.hiredate,'HH24:MI')); ---this displays 00:00
END;
so after execution of the above proc the hiredate field instead of showing the date (17-DEC-80) should show '06:30' for emp whose empno=7369;
|
|
|
|
| Re: date and time problem [message #87750 is a reply to message #87733] |
Mon, 07 February 2005 01:17 |
fanni
Messages: 96
Registered: March 2004
|
Member |
|
|
Hello Swapna
I have keenly observed the behavior of date and time problem and got the following findings
1) Colmn having 'Date' field is stored in the database with the format 'DD/MM/YYYY HH24:MI:SS', irrespective of the fact we have spplied any of its component or not.
2) If any of the date component is missing, by defalt Oracle stores the lowest possible value of that component.
e.g
Date Component Lowest Value
----------------- -------------
MM 1
DD 1
YYYY year of the server
HH24 0
MI 0
SS 0
So this is the whole story.
I am pasting some of the results, i used to reach this conclusion
--------------
UPDATE EMP
SET HIREDATE = TO_DATE('3','dd')
WHERE EMPNO = '7934';
03/01/2004 00:00:00 7934
----------------
1 UPDATE EMP
2 SET HIREDATE = TO_DATE('20-jan-2004 25','dd/mm/yyyy MI')
3* WHERE EMPNO = '7934'
20/01/2004 00:25:00 7934
-----------------
UPDATE EMP
SET HIREDATE = TO_DATE('20-jan-2004 06:30','dd/mm/yyyy hh24:MI')
WHERE EMPNO = '7934'
20/01/2004 06:30:00 7934
------------------
I wish you have got the reason for your problem.
Wish you all the best and best regards.
farhan
|
|
|
|
| Re: date and time problem [message #87755 is a reply to message #87748] |
Mon, 07 February 2005 06:29 |
ÇÊ
Messages: 15
Registered: September 2002
|
Junior Member |
|
|
I sort of see what you are trying to do, but I have to ask the following question:
What exactly are you trying to accomplish here?
In your example, you stated that you want the hire date to show 06:30. Now I understand you read the 06:30 from a temp table, but where did it come from to begin with?
Are you trying to do just a one-time update of all hiredates in your table? If so, then this method isn't the best route to take. In fact, I would venture to say regardless of what you're doing this probably isn't the best route.
|
|
|
|
| Re: date and time problem [message #87759 is a reply to message #87755] |
Mon, 07 February 2005 20:56 |
Swapna C
Messages: 6
Registered: February 2005
|
Junior Member |
|
|
Hi,
As i cannot put down exactly my proj related tables and logic behing this, i used emp table and some temptime table to shoot my question. The temptime table will be having some default values. My form is having a field which is defined as text item and the datatype is set to char. That field is used to capture the time start and time end of a job. since the emp table has a date field, i used this table and field in my example.
If you are looking only at the emp table and temptime which i have mentioned in my question then no doubt its not a good approach, coz there is no logic behind this,
Let me tell you some part of my form logic in simple terms , when the time start and end are not mentioned by the user then it will be defaulted to the standards mentioned for that job in some other table.
hope u are getting my point,
regards
swapna
|
|
|
|
| Re: date and time problem [message #87763 is a reply to message #87755] |
Mon, 07 February 2005 23:22 |
Swapna C
Messages: 6
Registered: February 2005
|
Junior Member |
|
|
Hi,
As i cannot put down exactly my proj related tables and logic behing this, i used emp table and some temptime table to shoot my question. The temptime table will be having some default values. My form is having a field which is defined as text item and the datatype is set to char. That field is used to capture the time start and time end of a job. since the emp table has a date field, i used this table and field in my example.
If you are looking only at the emp table and temptime which i have mentioned in my question then no doubt its not a good approach, coz there is no logic behind this,
Let me tell you some part of my form logic in simple terms , when the time start and end are not mentioned by the user then it will be defaulted to the standards mentioned for that job in some other table.
hope u are getting my point,
regards
swapna
|
|
|
|
| Re: date and time problem [message #87764 is a reply to message #87759] |
Tue, 08 February 2005 00:57 |
Swapna C
Messages: 6
Registered: February 2005
|
Junior Member |
|
|
Hi All,
thanks for the reponses posted to my question.
I would like to close this question now, as i got a solution to my problem.
I have changed the datatype of the field from date to datetime as date will not store the time component
regards
swapna
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
