469,125 Members | 1,640 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

Time datatype in Oracle Database

Hi Guys,

I have a table where I where I can enter date in HH:MM format.

I have already created the table as:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE "test"."SLA"
  2.   (
  3.     "SLAID"    NUMBER(10,0) NOT NULL ENABLE,
  4.     "SLALEVEL" VARCHAR2(200 BYTE) NOT NULL ENABLE,
  5.     "WEEKDAY_START_TIME" TIMESTAMP (6),
  6.     "WEEKDAY_END_TIME" TIMESTAMP (6),
  7.     "WEEKEND_START_TIME" TIMESTAMP (6),
  8.     "WEEKEND_END_TIME" TIMESTAMP (6),
  9.   )
  10.  

How can I format the TIMESTAMP datatype so I can enter values like 23:45 or 11:55 etc?

Currently if I try to do so I get the error:

Row 1: ORA-01847: day of month must be between 1 and last day of month
ORA-06512: at line 1



I have tried declaring them as:

Expand|Select|Wrap|Line Numbers
  1. "WEEKDAY_START_TIME" TIMESTAMP 'HH24:MM'
  2.  
with no luck. Any help will be appreciated.

Thanks.
Nov 21 '11 #1

✓ answered by rski

Well I think Oracle has not any datatype for storing only hours and minutes. When you use timestamp date also will be stored.

3 2707
rski
700 Expert 512MB
What do you mean
How can I format the TIMESTAMP datatype so I can enter values like 23:45 or 11:55 etc?
What do you mean format?

Timestamp is used for storing date with time.
So you can insert value in the following way
Expand|Select|Wrap|Line Numbers
  1. insert into table values(to_timestamp('23:40','HH24:Mi'))
  2.  
Nov 21 '11 #2
Well I was using Oracle SQL developer to enter values (not using the INSERT query) manually in a data entry table just like in a spreadsheet.

And I get the error why I try to commit the changes:

Row 1: ORA-01847: day of month must be between 1 and last day of month
ORA-06512: at line 1


By formatting I meant tweaking the TIMESTAMP datatype in ALTER TABLE statement in such a way so I can enter the values without the above error.

Something like this (just as example, I know this is wrong)

Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE
  2. MODIFY column_1 TIMESTAMP='HH24:MM'
  3.  
Something like that or any other Datatype that can solve this problem.


Many thanks. But I guess INSERT statement is one way around it.
Nov 21 '11 #3
rski
700 Expert 512MB
Well I think Oracle has not any datatype for storing only hours and minutes. When you use timestamp date also will be stored.
Nov 21 '11 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by f | last post: by
4 posts views Thread by =?Utf-8?B?U3RlcGhhbmllIERvaGVydHk=?= | last post: by
reply views Thread by Jack | last post: by
reply views Thread by Winder | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.