Re: I need a SQL...

On Thu, 2003-09-11 at 14:07, Andrew L. Gould wrote:

> On Thursday 11 September 2003 06:25 am, Bjørn T Johansen wrote:
> > I need to write a SQL that calculates the interval between a start time
> > and a stop time. This is the easy part. The problem is that I only have
> > the time part, i.e. no date, so how can I be sure to also calculate the
> > interval if the start time is before midnight and the stop time is after
> > midnight?
> >
> >
> > Regards,
> >
> > BTJ
>
> If the activity or period you are measuring can equal or exceed 12 hours, you
> won't be able to calculate it reliably without a start date and a stop date.
> If the periods are always less than 12 hours (and you assume all the data is
> good), then stop times that are less than start times would indicate an
> intervening midnight.
>
> The dates do not have to be in the same fields as the times, since you can add
> date and time data to create a timestamp for datetime calculations:
>
> (stop_date + stop_time) - (start_date + start_time)
>
> Best of luck,
>
> Andrew Gould

Yes, the period can exceed 12 hours, so what you are saying is that this is not possible to solve
without the date part? I can write this logic in my business logic but I was hoping to
solve this in my database layer...

BTJ