OraFAQ Forum: SQL & PL/SQL » To store just Time information in Oracle column

Home » SQL & PL/SQL » SQL & PL/SQL » To store just Time information in Oracle column (Oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

To store just Time information in Oracle column [message #327260]

Sun, 15 June 2008 16:00

thamarais
Messages: 2
Registered: June 2008

Junior Member

Hi All

I am moving data from MS Access to Oracle.
In MS Access, we have Date (Short Date) field stores just time information like 07:00 AM, 12:29 PM etc.
While moving data from MS Access to Oracle (Date datatype), along with time values (07:00 AM, 12:29 PM), some default date also is getting stored.
The target value getting stored currently is 12/31/1899 07:00 AM, 12/31/1899 12:29 PM.

I don't want 12/31/1899, I need just time information. How to achieve this one?

Thanks

Report message to a moderator

Re: To store just Time information in Oracle column [message #327261 is a reply to message #327260]

Sun, 15 June 2008 16:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above

(re)search TO_CHAR() and TO_DATE() functions

[Updated on: Sun, 15 June 2008 17:24] by Moderator

Report message to a moderator

Re: To store just Time information in Oracle column [message #327281 is a reply to message #327260]

Sun, 15 June 2008 23:22

Frank
Messages: 7901
Registered: March 2000

Senior Member

thamarais wrote on Sun, 15 June 2008 23:00

I don't want 12/31/1899, I need just time information. How to achieve this one?

Oracle does not know a datatype that stores only times. In Oracle you use the DATE datatype, which always holds both the date- and the time-part of a moment in time.
Like Anacedent said, you can use to_date to store a time-portion in a DATE-type field, and to_char to display it in any form you want (e.g. only the time part).

Report message to a moderator

Re: To store just Time information in Oracle column [message #327282 is a reply to message #327260]

Sun, 15 June 2008 23:24

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

There is no TIME datatype in Oracle... but what is a time without a date? Are you sure you will meet your correspondant if you take an appointement at 4PM without saying which day?

Regards
Michel

Report message to a moderator

Re: To store just Time information in Oracle column [message #327287 is a reply to message #327260]

Sun, 15 June 2008 23:35

saikumar_mudigonda
Messages: 23
Registered: June 2008
Location: hyderabad

Junior Member

Is there any specific reason, that you choosen the destination data type is date (oracle) for storing only time.

and i agree with Michel & Frank, you can store both date and time, but while retrieving you can take what ever you want.

thanks

[Updated on: Sun, 15 June 2008 23:38]

Report message to a moderator

Re: To store just Time information in Oracle column [message #327289 is a reply to message #327287]

Sun, 15 June 2008 23:37

Frank
Messages: 7901
Registered: March 2000

Senior Member

saikumar_mudigonda wrote on Mon, 16 June 2008 06:35

Is there any specific reason, that you choosen the destination data type is date (oracle) for storing only time.

thanks

Please explain what type YOU would have chosen..

Report message to a moderator

Re: To store just Time information in Oracle column [message #327291 is a reply to message #327289]

Sun, 15 June 2008 23:41

saikumar_mudigonda
Messages: 23
Registered: June 2008
Location: hyderabad

Junior Member

My intention is, if he need only time to be stored and there will not be any modifications happen to that in future. he can take char set datatype.

Just out of intrest what kind of situation he is in to store this time stamp.

thanks

Report message to a moderator

Re: To store just Time information in Oracle column [message #327298 is a reply to message #327291]

Mon, 16 June 2008 00:06

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

saikumar_mudigonda wrote on Mon, 16 June 2008 06:41

My intention is, if he need only time to be stored and there will not be any modifications happen to that in future. he can take char set datatype.

Just out of intrest what kind of situation he is in to store this time stamp.

thanks

The most appropriate datatype depends on what the needs are.
It is most likely it is NOT a string unless time is only for display purpose and you want it in a fixed format.

Regards
Michel

Report message to a moderator

Re: To store just Time information in Oracle column [message #327678 is a reply to message #327260]

Tue, 17 June 2008 03:54

dr.s.raghunathan
Messages: 540
Registered: February 2008

Senior Member

hi,
substr(to_char(col_name, 'dd-mm-yyyy hh:mm am'),12,8)
will fetch your desired value provided col_name is date type
yours
dr.s.raghunathan

[EDITED by LF: disabled smilies in this message]

[Updated on: Tue, 17 June 2008 04:30] by Moderator

Report message to a moderator

Re: To store just Time information in Oracle column [message #327682 is a reply to message #327678]

Tue, 17 June 2008 04:15

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Why substr? just remove date part of to_char!

Regards
Michel

Report message to a moderator

Re: To store just Time information in Oracle column [message #327692 is a reply to message #327678]

Tue, 17 June 2008 04:38

world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad

Member

By using to_char function we can get the time from date value.

For example the following select statement gives time value.


select to_char(sysdate, 'hh:mi:ss') from dual

[Updated on: Tue, 17 June 2008 04:39]

Report message to a moderator

Re: To store just Time information in Oracle column [message #327805 is a reply to message #327678]

Tue, 17 June 2008 13:32

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

dr.s.raghunathan wrote on Tue, 17 June 2008 04:54

hi,
substr(to_char(col_name, 'dd-mm-yyyy hh:mm am'),12,8)
will fetch your desired value provided col_name is date type

Not really. First, go with Michel's suggestion, but use the correct format for TIME. You are using hour and month for the time portion.

[Updated on: Tue, 17 June 2008 13:33]

Report message to a moderator

Re: To store just Time information in Oracle column [message #328023 is a reply to message #327805]

Wed, 18 June 2008 11:24

thamarais
Messages: 2
Registered: June 2008

Junior Member

Thanks for the replies.

Front end is ASP pages and management is reluctant to do any changes in front end. All they want to do is backend migration and minimal changes in front end.

All they want is to store just Time information in Oracle database. Because currently in MS Access they are storing with datatype (Date - Short date).
I suggested to define column as Varchar2 or Char. Here the issue is - It takes any value like 25:03:00. It should not accept beyond 24 hrs.
I tried to set constraint in varchar2 or char, but not possible.

Any help please.

Thanks

Report message to a moderator

Re: To store just Time information in Oracle column [message #328025 is a reply to message #327260]

Wed, 18 June 2008 11:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I suggested to define column as Varchar2 or Char
Bad, Bad, bad, bad idea, but you are free to continue to dig your own grave.

Report message to a moderator

Re: To store just Time information in Oracle column [message #328034 is a reply to message #328025]

Wed, 18 June 2008 12:13

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

I wonder what actually WOULD be a good solution.

Since I had quite a few annoying problems in an application that stored "season" start/end dates as dates and only used the day/month part and discarded the year and time part in the input and display I would also be weary to use a date field for storage.

(Some version of the frontends inserted 1900/00:00:00, other versions the current year and time, and so on, so it was awkward to work with in custom reports and extensions we made.)

I would probably consider using a number for "seconds since midnight", and add a constraint that only numbers < 24 * 60 * 60 can be entered for storage of a "time only" value.

Of course it would depend on what I needed to actually do with this time information.

[Updated on: Wed, 18 June 2008 12:15]

Report message to a moderator

Previous Topic:	Transaction Count on a Database for a given day
Next Topic:	Better way of writing the code

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 19:41:20 CDT 2024