Re: How to TRUNC ???

From: Jim Lyons <jlyons_at_weblyons.com>
Date: 16 Oct 2001 15:16:29 -0700
Message-ID: <e67857c9.0110161416.1b211fac@posting.google.com>

"Yuval Sverdlik" <ysverdlik_at_nj.rr.com> wrote in message news:<VDKy7.13196$T5.2327301_at_typhoon.nyc.rr.com>...
> I have a table that I'm creating. (a customer table) I need to figure out
> how to get a constraint where a customer can have only one order per day.
> Here is the table below. I know that if I TRUNC the date it will make the
> time portion of the date to 12:00am and therefore if sysdate is used the
> date will still be the same throughout the day.
>
> Now if I use sysdate I can insert two of the same customers with sysdate
> because the date is really not the same even if i did it at the same time.

Why not TRUNC sysdate? Have a unique index on id and date. Insert a new record with index values (id, trunc(sysdate)). If you do this twice in the same day, you'll get a duplicate value error.

Question: if the customer does it twice in the same 24-hour period but in different time-zones, what would you do?

Jim Lyons
UT Austin Received on Tue Oct 16 2001 - 17:16:29 CDT