At 01:42 AM 19/12/2009, Vishal Tiwari wrote:

>But let me explain once again :
>
>Say for example one employee's entry and exit time may be multiple in a day that's why I need only one date and time column based on this i will do rest calculation.

That doesn't make sense. If an employee has multiple periods of working throughout a day then you will need multiple "start-stop" records per employee, one for each period. These have to be timestamps, since a night-shift worker will certainly have periods that start on one *date* and finish on another *date*.

>because i don't know how many times employee might have gone out side and again came in for the same day.

If you cannot capture that information (time-clock, hours book, etc.) then where would that information come from?

Your "clock in and clock out" records *are* the data you will need to work with. Your system must protect itself from events such as
-- employees that clock in but do not clock out (or vice versa)
-- employees that come in through the window and bypass the clock-in
-- clocks/computers/employees that break down

etc., etc.

So what I am telling you is that you have to DESIGN a time recording system that will work for people, hardware and software and is capable of highlighting any kinds of wrong behaviour.

>so, as per this i think one date and one time column is enough.

Enough for what?

>Now as per following columns how i will calculate the total time employee had worked for a day (i.e. for each day).
>Â
>This time period is required in the hours and minutes, for each employee for each day.
>
>1) EmpNo Varchar(15)
>2) dtDate Date
>3) tmTime Time
>
>Plz. let me know your opinion.

My opinion is that you need to pay attention to what people are telling you and give a comprehensible description of your requirements. We have told you that TIME means "time of day" and cannot be used to record an interval of time in e.g. hours and minutes. IT DOES NOT AND CANNOT WORK THAT WAY. The Lords of the SQL Standard ordain it so.

If you believe that a record consisting of just a date and a number of hours worked is in some way useful for what you want to do, then make that third field a SMALLINT representing minutes worked (calculated by a guy standing by the door with a stopwatch and a shotgun) or maybe a DECIMAL(9,4) which gives you a way to record hours and part-hours.

If you genuinely want useful answers, ask technically sensible questions. Try reading this:

http://www.catb.org/~esr/faqs/smart-questions.html

./heLen