Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Error DateTime Calculations

Posted on 2011-05-05
12
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
Comment
Question by:NerishaB
  • 4
  • 4
  • 4
12 Comments
 
LVL 3

Expert Comment

by:lisa_mc
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

by:lisa_mc
ID: 35698153
(6,2) would allow 9999.99 and so on
0
 
LVL 15

Expert Comment

by:tim_cs
ID: 35698170
What date are you using for the DefaultStart?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:NerishaB
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

by:tim_cs
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

by:
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

by:NerishaB
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

by:tim_cs
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

Open in new window

0
 

Author Comment

by:NerishaB
ID: 35698713
No, I cant add a work day Field.  
0
 
LVL 3

Expert Comment

by:lisa_mc
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

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

Author Closing Comment

by:NerishaB
ID: 35719946
thanks
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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

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.

Join & Ask a Question