Home » SQL & PL/SQL » SQL & PL/SQL » Time and date question
Time and date question [message #389372] Sun, 01 March 2009 11:03 Go to next message
mustaine85
Messages: 7
Registered: February 2009
Junior Member
Hi, Im creating a table that stores the start and finish time for an event and I need to store a 24 hour time.
I don't quite understand how the data type date works. Can you set the format when declaring the column? Or do you have to insert data in a specific format?
Could I validate it with a substr check constraint?
Also could I store the date and the time in one field?
Thanks for any help.

10.2.0.1.0
Win XP
Re: Time and date question [message #389373 is a reply to message #389372] Sun, 01 March 2009 11:04 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
When all else fails, Read The Fine Manual
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#sthref3828
Re: Time and date question [message #389374 is a reply to message #389372] Sun, 01 March 2009 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database SQL Reference
Chapter 2 Basic Elements of Oracle SQL
Section Datatypes
Paragraph DATE Datatype

Regards
Michel
Re: Time and date question [message #389384 is a reply to message #389372] Sun, 01 March 2009 13:57 Go to previous messageGo to next message
mustaine85
Messages: 7
Registered: February 2009
Junior Member
Thanks for the replies, excuse my ignorance, I'm very new to Oracle and still trying to understand it.
I have read about the date type countless times but I don't quite understand how it works. I'm getting really confused, I tried using:
TO_DATE('1998/05/31 12:00:00', 'YYYY/MM/DD HH24:MI:SS')
in an insert command and the MI produces an error
Is there a way to store just a time value? or set formatting rules when creating a table?


Re: Time and date question [message #389385 is a reply to message #389372] Sun, 01 March 2009 14:03 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Re: Time and date question [message #389386 is a reply to message #389384] Sun, 01 March 2009 14:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
in an insert command and the MI produces an error

If you don't tell us the error we can't help.
What is MI?

Regards
Michel
Re: Time and date question [message #389388 is a reply to message #389372] Sun, 01 March 2009 14:10 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>in an insert command and the MI produces an error
ERROR? What error? I don't see any error.

>Is there a way to store just a time value?
Yes

>or set formatting rules when creating a table?
Formatting is done for data presentation; not for data storage.

[Updated on: Sun, 01 March 2009 14:11]

Report message to a moderator

Re: Time and date question [message #389391 is a reply to message #389372] Sun, 01 March 2009 14:45 Go to previous messageGo to next message
mustaine85
Messages: 7
Registered: February 2009
Junior Member
Sorry about the incoherent questions, I'm just a bit confused but basically I need suggestions on the best way to handle a simple booking system and what data types to use.

>Is there a way to store just a time value?
Yes
Could you tell me how please Smile


I'm just listing the attributes that are relevant to my questions.
create table room_booking (
book_date date,
start_time date, -- Would like to store just a time value here
finish_time date -- and here
)
/


10.2.0.1.0
Win XP
Re: Time and date question [message #389392 is a reply to message #389372] Sun, 01 March 2009 14:55 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>start_time date, -- Would like to store just a time value here
Why just time? Why not date & time combined?


How will your application handle the case where a room is required from 23:30 until 00:30 next day?
Re: Time and date question [message #389459 is a reply to message #389372] Mon, 02 March 2009 03:16 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
What about a proper data type as Interval Day to Second.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i78291

Bye Alessandro
Previous Topic: Exporting Function output return type sys_refcursor(Result set) to Excel sheet
Next Topic: Connecting to scott schema
Goto Forum:
  


Current Time: Sat Dec 10 12:48:22 CST 2016

Total time taken to generate the page: 0.07136 seconds