Formatted SysDate in default value

Question

in Oracle, (with pl-sql developer) How can I use a formatted default value in a column? (like this: to_char(sysdate, 'HH:MM:SS') )

I want create this coumns >>

name: f1

type: date

default: to_char(sysdate, 'HH:MM:SS')

A DATE column does not have a "format" – Feb 18 '13 at 07:39 — , Feb 18 '13 at 07:39

score 4 · Answer 1 · answered Feb 18 '13 at 07:04

4

Oracle doesn't have TIME only columns. Even if you set a column or variable to just HH24:MI:SS it will still have a date element.

The problem with just storing a time is that it is rarely useful without a date to go with it. So why not just store the datetime in a single column?

But if you really, really, really want just the time store the "seconds after midnight" as a number:

f1 number(5,0) default to_number(to_char(sysdate, 'sssss'))

answered Feb 18 '13 at 07:04

APC

144,005
19
170
281

2

You *could* also use [an interval](http://stackoverflow.com/a/12216981/266304) rather than a number, e.g. `f1 interval day to second(0) default systimestamp - trunc(systimestamp)`, which might be slightly clearer to read, but harder to work with... but also only if you really, really, really want the time on its own. – Alex Poole Feb 18 '13 at 09:44

score 1 · Answer 2 · answered Feb 18 '13 at 07:03

Date type value does not depend on formatting.
Only when converting date to string, formatting is involved.
You can create formatted column of string values:

create table t (
  id int,
  f1 varchar2(20) default to_char(sysdate, 'HH24:MI:SS')
);

SqlFiddle

Formatted SysDate in default value

2 Answers2