Re: Using a sequence to force unique DATE field

In article <3sprt1$nm1_at_newstand.syr.edu>, eastephe_at_mailbox.syr.edu says...
>
> We currently have a set of two tables that are linked together
> by only a DATE field. Usually, the field can be unique but since
> O doesn't support milliseconds with the DATE, we sometimes
> run into PK violations. (And yes, we should have used a SEQUENCE with a
> NUMBER type column, but that's another story.)
>
> What I'd like to do is stuff the following value into the DATE:
>
> to_date(forrest_gump.nextval,'sssss')
>
> where forrest_gump is a sequence ranging from zero to
> 86399. The expression above returns the date as being 6/1/95
> and then an appropriate number of seconds based on the
> sequence. Is that normal? Anyone know any better tricks to
> hack around this problem?

A simplier way to do this is to use

trunc(pk_date) + forrest_gump.nextval / 86400

when you do your insert. This eliminates any need for to_date() and to_char(). Mind you that this is still not going to entirely solve your problem since you are limited to 86400 records per day.

--
"And this is all I have to say about that..."   - F. Gump
      ___
     (o o)
+-oo0-\_/-0oo---------------------------------------------------------------+
|  Alvin W. Law ..... Oracle Corporation ....... Email: alaw_at_us.oracle.com  |
+---------------------------------------------------------------------------+

ORA-03113: end-of-file on communication channel