asked on

SQL Error DateTime Calculations

Hi,

I have a piece of query that looks like this:

TotalHours_Worked = CAST(datediff(minute, Min(ClockTime), DefaultStart) / 60.0 as decimal(4,2)),

where ClockTime is a time that a user logged in in the morning, and defaultStart is the time that work actually starts. This query is created to find out how long the person worked before work actually starts.

I am getting the following error:

"Arithmetic overflow error converting numeric to data type numeric."

What am I doing wrong?

lisa_mc

hi

decimal (4,2) doesnt allow a value greater than 99.99 try decimal (6,2) or higher

see if that helps

lisa_mc

(6,2) would allow 9999.99 and so on

tim_cs

What date are you using for the DefaultStart?

NerishaB

ASKER

I tried using (6,2), but the result I am getting is not correct.

DefaultStart, in this case will be 2011/04/18 18:00:00
The ClockInTime in this case is 2011/04/18 17:56:24

tim_cs

Whenever I test that out hard coding in the date and times it works for me.

Could you select just the DefaultStart and ClockInTime to make sure they are both being pulled back as 4/18?

ASKER CERTIFIED SOLUTION

lisa_mc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NerishaB

ASKER

I am struggling with the fact that the person clocks in on one day, but clocks out the following day, ie, he works night shift. Any idea how to overcome this?

tim_cs

Any chance of adding in a "Work Date" to your table that could stay the same for each ClockIn event for the day? Something like what is below?

Work Day   |   ClockDateTime   |  Event
4/18/2011  |   4/18/2011 17:84 |  Clock In
4/18/2011  |   4/19/2011 01:23 |  Lunch
4/18/2011  |   4/19/2011 04:30 |  Clock Out

Open in new window

NerishaB

ASKER

No, I cant add a work day Field.

lisa_mc

hi

using the datediff(minute, time1, time2) will report number of minutes over different days you could then divide this by 60 to calculate total hours worked

what are you having trouble with

tim_cs

Can we see more of your query/proc to get a better idea of what you're doing?

NerishaB

ASKER

thanks