Database design question with time

[Jim]

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

-Jim

[Erland Sommarskog]

Jim (ji********@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

>> ... 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.

[Jim]

Thanks Ill try that

-Jim
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Jim (ji********@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))