This content has been marked as final. Show 12 replies
the data type should be date
in the forms field's property, set the format mask to HH:MM:SS
you will get the desired result.
Why don't you save it as date field itself and use format mask with date field on forms.
i've tried to build a "date" column as you've said and changed the format mask of the field based on that column to hh24:mm:ss , and it is working if you try to insert the time into the field ,i tried to insert "11:11:11" into the field , but unfortunately , when i query the table from the data base , it show "01/11/13" , and i do not know why , and offcorse i do not want that , i want the time i inserted to be shown"11:11:11"
and please do not tell me it is a normal thing , because may be i should do calculations on this field and another .
thanks a lot
Edited by: semsem on Jan 6, 2013 2:56 AM
dear you used hh:mm:ss which is wrong as mm is for months you shuld use MI for minutes as HH24:MI:SS or HH:MI:SS
actually date format is as
the result after modifying the format to hh24:mi:ss is the same "01/11/13" ,
i want the time i enter to be shown as it is inserted 11:11:11 when i query it from the isqlplus?
you have to access the value as to_char(datefield,'hh24:mi:ss') as the timestamp will go to date field and it will show you the date only.
for time you need to access to_char as given above...
The time is only a part of a date. When you query a date (or better date/time-column) you have to define in what format you want to see the date. So you have either to change the settings of your tool so that it shows the data in the way you want, or you have to explicitly cast the date-column to a string using TO_CHAR with the appropiate format-mask.
i thought of a pre-insert trigger and write in it :
to_char(datefield,'hh:mi') but if i can insert it like in the database , a nother problem pops up which is i can not do calculations on it .(-,+,*,/) , and may be i want this .
No, you definitely should not store the time as a char. Store is as a date as already suggested.
so you have either to change the settings of your tool so that it shows the data in the way you wanthow to change the settings of sqlplus or isqlplus to show the data in the way i want ?
ever heard of google? It returns thousands of hits when you search for "oracle sqlplus dateformat"
Google will give you many links if you do as Andreas siad :)
Set the date format for the sqlplus session as you want
Now run the query
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
select to_char(date_column,'hh24:mi:ss') from table_name