Home » SQL & PL/SQL » SQL & PL/SQL » Time - Datatypes question (10g)
Time - Datatypes question [message #391265] Wed, 11 March 2009 10:22 Go to next message
msevani
Messages: 12
Registered: August 2005
Junior Member
HI,

what is the best data type to use for a time value in oracle?

varchar2 or date or something else all together?

i created a test table where i set the datatype to DATE and i tried inserting a time into it but it didn't work.

insert into booking
(showtime)
values
(to_date('21:02:44', 'hh24:mi:ss'));

select * from booking;

01-MAR-09


so how can i enter a time value?
Re: Time - Datatypes question [message #391267 is a reply to message #391265] Wed, 11 March 2009 10:25 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
select to_char(showtime, 'hh24:mi:ss') from booking

Or look at the intervaldaytosecond datatype
Re: Time - Datatypes question [message #391268 is a reply to message #391265] Wed, 11 March 2009 10:30 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>so how can i enter a time value?
You already did it correctly.
Re: Time - Datatypes question [message #391269 is a reply to message #391265] Wed, 11 March 2009 10:31 Go to previous messageGo to next message
msevani
Messages: 12
Registered: August 2005
Junior Member
thanks, but what data type is best used for a time value?
Re: Time - Datatypes question [message #391270 is a reply to message #391265] Wed, 11 March 2009 10:34 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> what data type is best used for a time value?
DATE datatype is best so arithmetic operations can be done on them & comparisons across day boundaries are valid.
Re: Time - Datatypes question [message #391271 is a reply to message #391265] Wed, 11 March 2009 10:38 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@msevani,

Search For Datetime and Interval Datatypes in Oracle Datatypes

Hope this helps,

Regards,
Jo
Re: Time - Datatypes question [message #391272 is a reply to message #391265] Wed, 11 March 2009 10:38 Go to previous messageGo to next message
msevani
Messages: 12
Registered: August 2005
Junior Member
thanks i've noticed the follwing

when i write the query "select to_char(showtime, 'DD-MM-YY hh24:mi:ss') from booking"

the output is "11-03-09 15:33:03"

fair enough that ok.

BUT if i "select * from booking"

the output is "11-MAR-09"

how come it does not show the full value "11-03-09 15:33:03"..i.e. with the time?

In my insert, i enetred SYSTIMESTAMP which worked fine
Re: Time - Datatypes question [message #391273 is a reply to message #391272] Wed, 11 March 2009 10:43 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Why do you think it happens? Did you bother to read my reply at the top of the thread?
Re: Time - Datatypes question [message #391274 is a reply to message #391265] Wed, 11 March 2009 10:43 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
showtime is type DATE.
What gets displayed is STRING characters.
Implicit data type conversion occurs.
NLS_DATE_FORMAT controls the default conversion of DATE to VARCHAR2.
You either change NLS_DATE_FORMAT or explicitly use TO_CHAR to display the results as you desire it.

[Updated on: Wed, 11 March 2009 10:44]

Report message to a moderator

Previous Topic: To print the column value and a string
Next Topic: SQL Model Clause to Create Resultset without DDL or Collection
Goto Forum:
  


Current Time: Sun Dec 04 04:43:04 CST 2016

Total time taken to generate the page: 0.13950 seconds