Datatype for interval/timespan

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Seb - 19 Jul 2008 18:20 GMT

I'm creating a table with activities. These activities are defined by
a name, a start datetime and a interval. The two first types are
obvious. But I'm unsure abut the interval type.

Initially I used a int column holding the number of seconds a given
interval spanned. This is pretty simple when inserting and easy to
handle activities spanning multiply days. But this is problematic when
used with asp.net's gridviews and such, in most causes I need to
format it as hh:mm:ss.

Now I use a datetime column which is easy to format but the mmddyyyy
part is left empty and activities spanning multiply days... well it
isn't pretty.

Anyone got a suggestion on how to handle this data issue?

best regards,
seb

Reply to this Message

Tom Cooper - 19 Jul 2008 18:47 GMT

One way would be to keep a start datetime and an end datetime instead of an
interval. It's easy to calculate the end datetime from the start datetime
and the interval by using the DateAdd() function, and given the start and
end datetimes, easy to get the interval value back by using the DateDiff()
function.

Tom

> I'm creating a table with activities. These activities are defined by
> a name, a start datetime and a interval. The two first types are
[quoted text clipped - 14 lines]
> best regards,
> seb

Reply to this Message

Seb - 19 Jul 2008 22:27 GMT

Thanks for the reply. That solved my problem.

seb

On Jul 19, 7:47 pm, "Tom Cooper"
<tomcoo...@comcast.no.spam.please.net> wrote:

> One way would be to keep a start datetime and an end datetime instead of an
> interval. It's easy to calculate the end datetime from the start datetime
[quoted text clipped - 22 lines]
> > best regards,
> > seb

Reply to this Message

Andrew J. Kelly - 19 Jul 2008 23:17 GMT

And going forward you might want to have a look at SQL2008 and the new Time
datatype as well.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

Thanks for the reply. That solved my problem.

seb

On Jul 19, 7:47 pm, "Tom Cooper"
<tomcoo...@comcast.no.spam.please.net> wrote:

> One way would be to keep a start datetime and an end datetime instead of
> an
[quoted text clipped - 27 lines]
> > best regards,
> > seb

Reply to this Message

Seb - 20 Jul 2008 13:48 GMT

> And going forward you might want to have a look at SQL2008 and the new Time
> datatype as well.

Thanks, I read about it, but since my activities can span multiply
days this isn't relevant for this case. But thanks for the advice :)

seb

Reply to this Message