Time datatype in Oracle Database

[Osiris Dragon]

Hi Guys,

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

I have already created the table as:

CREATE TABLE "test"."SLA"
  (
    "SLAID"    NUMBER(10,0) NOT NULL ENABLE,
    "SLALEVEL" VARCHAR2(200 BYTE) NOT NULL ENABLE,
    "WEEKDAY_START_TIME" TIMESTAMP (6),
    "WEEKDAY_END_TIME" TIMESTAMP (6),
    "WEEKEND_START_TIME" TIMESTAMP (6),
    "WEEKEND_END_TIME" TIMESTAMP (6),
  )

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:

"WEEKDAY_START_TIME" TIMESTAMP 'HH24:MM'

with no luck. Any help will be appreciated.

Thanks.

[rski]

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

insert into table values(to_timestamp('23:40','HH24:Mi'))

[Osiris Dragon]

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)

ALTER TABLE
MODIFY column_1 TIMESTAMP='HH24:MM'

Something like that or any other Datatype that can solve this problem.


Many thanks. But I guess INSERT statement is one way around it.

[rski]

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