Home » SQL & PL/SQL » SQL & PL/SQL » To store just Time information in Oracle column (Oracle)
To store just Time information in Oracle column [message #327260] Sun, 15 June 2008 16:00 Go to next message
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
Re: To store just Time information in Oracle column [message #327261 is a reply to message #327260] Sun, 15 June 2008 16:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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 Go to previous messageGo to next message
Frank
Messages: 7880
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).
Re: To store just Time information in Oracle column [message #327282 is a reply to message #327260] Sun, 15 June 2008 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: To store just Time information in Oracle column [message #327287 is a reply to message #327260] Sun, 15 June 2008 23:35 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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..
Re: To store just Time information in Oracle column [message #327291 is a reply to message #327289] Sun, 15 June 2008 23:41 Go to previous messageGo to next message
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
Re: To store just Time information in Oracle column [message #327298 is a reply to message #327291] Mon, 16 June 2008 00:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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

Re: To store just Time information in Oracle column [message #327678 is a reply to message #327260] Tue, 17 June 2008 03:54 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why substr? just remove date part of to_char!

Regards
Michel
Re: To store just Time information in Oracle column [message #327692 is a reply to message #327678] Tue, 17 June 2008 04:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4643
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 Go to previous messageGo to next message
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

Re: To store just Time information in Oracle column [message #328025 is a reply to message #327260] Wed, 18 June 2008 11:26 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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.
Re: To store just Time information in Oracle column [message #328034 is a reply to message #328025] Wed, 18 June 2008 12:13 Go to previous message
ThomasG
Messages: 3189
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:
  


Current Time: Thu Dec 08 21:59:24 CST 2016

Total time taken to generate the page: 0.05311 seconds