Learn how to a build a cloud-first strategyRegister Now

x
Solved

# SQL Error DateTime Calculations

Posted on 2011-05-05
Medium Priority
187 Views
Last Modified: 2012-05-11
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?
0
Question by:NerishaB
• 4
• 4
• 4
12 Comments

LVL 3

Expert Comment

ID: 35698148
hi

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

see if that helps
0

LVL 3

Expert Comment

ID: 35698153
(6,2) would allow 9999.99 and so on
0

LVL 15

Expert Comment

ID: 35698170
What date are you using for the DefaultStart?
0

Author Comment

ID: 35698204
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
0

LVL 15

Expert Comment

ID: 35698288
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?
0

LVL 3

Accepted Solution

lisa_mc earned 2000 total points
ID: 35698325
hi again

if your gettin the difference between these times which will give you an answer of 3.76 mins then why are you dividing by 60

maybe thats causing the error
0

Author Comment

ID: 35698592
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?
0

LVL 15

Expert Comment

ID: 35698646
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
``````
0

Author Comment

ID: 35698713
No, I cant add a work day Field.
0

LVL 3

Expert Comment

ID: 35698735
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
0

LVL 15

Expert Comment

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

Author Closing Comment

ID: 35719946
thanks
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differeâ€¦
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down â€¦
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
###### Suggested Courses
Course of the Month20 days, 23 hours left to enroll

#### 810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.