Database design question with time

Im designing a database where a user enters the date and the number of
hours and minutes he worked for the day..now i can do this

Workdate small date
Hours integer
Minutes integer

But then would I have to have the front end know that when adding up
the hours and minutes for the week that 60 minutes = 1 hour or is
there some way to do this in the database?

thanks

-JimJim (jim.ferris@.motorola.com) writes:
> Im designing a database where a user enters the date and the number of
> hours and minutes he worked for the day..now i can do this
> Workdate small date
> Hours integer
> Minutes integer
>
> But then would I have to have the front end know that when adding up
> the hours and minutes for the week that 60 minutes = 1 hour or is
> there some way to do this in the database?

You could have a computed column with the forumla Minutes + 60 * Hours:

CREATE TABLE workhours (
userid userid_type NOT NULL,
workdate smalldatetime NOT NULL,
hours tinyint NOT NULL,
minutes tinyint NOT NULL,
worked_minutes AS minutes + 60 * hours,
CONSTRAINT pk_workhours(userid, workdate))

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> ... database where a user enters the date and the number of hours
and minutes he worked for the day..<<

Use a duration instead of trying to create your own temporal datatype
system.

CREATE TABLE Timecard
(emp_id INTEGER NOT NULL,
start_time DATETIME NOT NULL,
finish_time DATETIME, -- null means still active
PRIMARY KEY (emp_id, start_time));

You can now use BETWEEN predicates and a calendar table.|||Thanks Ill try that

-Jim

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9474F24297CBCYazorman@.127.0.0.1>...
> Jim (jim.ferris@.motorola.com) writes:
> > Im designing a database where a user enters the date and the number of
> > hours and minutes he worked for the day..now i can do this
> > Workdate small date
> > Hours integer
> > Minutes integer
> > But then would I have to have the front end know that when adding up
> > the hours and minutes for the week that 60 minutes = 1 hour or is
> > there some way to do this in the database?
> You could have a computed column with the forumla Minutes + 60 * Hours:
> CREATE TABLE workhours (
> userid userid_type NOT NULL,
> workdate smalldatetime NOT NULL,
> hours tinyint NOT NULL,
> minutes tinyint NOT NULL,
> worked_minutes AS minutes + 60 * hours,
> CONSTRAINT pk_workhours(userid, workdate))