Solved

SQL Server and sum of Time

Posted on 2015-01-16
6
253 Views
Last Modified: 2015-01-22
Hello Experts Exchange
I have a SQL Server table made from data in Excel, in Excel the data is just a time field, but when I import into SQL Server the date of 30/12/1899 is added to the front of the time data.

I have a field that is called [Total Down Time] I want to sum the time of this field to give me a total of the repair time to fix the tool, however when I run a query to give me the sum of time the data is wrong.

This is the query I run.

Select [Tool Code], [Asset M/C N°], Sum(DateDiff(second, '30/12/1899 00:00:00',[Total Down Time])) as [Total Down Time],Count(*) as OCC
From [dbo].['Overall 2014 - 2015$']
Where [Bay N°] in ('1')
and [Reported Date] between '01/12/2014' and '31/12/2014'
and [Tool code] = 'S78'
Group by [Tool Code],[Asset M/C N°]

Open in new window



This is the result I get back.
Tool Code      Asset M/C N°      Total Down Time      OCC
S78                        S01-01                        1263480                        10

I have uploaded a csv file with the data that is in the database.

How do I get a sum of the Total down time field correctly?

Regards

SQLSearcher
Example-Data.csv
0
Comment
Question by:SQLSearcher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 5

Expert Comment

by:Leon Kammer
ID: 40553051
Hi,

1899-12-30 is the base date for datetime values supplied without a date component in sql server.

There are dates that are not 30/12/1899 in your dataset, there are 3 on different days, so Datediff is in fact calculating this correctly

So, to convert your time component to seconds you will need to either change the datatype to time or ensure that the date portion of the datetime column is constant, unless it was down for 5 days.
0
 
LVL 5

Accepted Solution

by:
Leon Kammer earned 500 total points
ID: 40553119
If you only want the time portion of the column, then something like
 Convert(Datetime,CAST([Total Down Time] as time(0))) will provide the time portion only of the datetime column to seconds and then convert this back to a standard datetime datatype.

Another solution is to eliminate the date portion altogether and use something like:
DateDiff(second,'00:00:00',Cast([Total Down Time] as Time(0)))

These two are assuming that the reported downtime is in fact less than one day.

You could also go down the route of a UDF, but if the data source is not huge, this this should be a quick and dirty fix.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40553438
Don't have a lot of details for you, but I had an airline for a client where I had to do time math, and many of the dates crossed over the midnight boundry.  So..
Need to do this as datetime columns and not date columns.
Vaguely remembering that in reporting it interpreted all {datetime} - {datetime in same day} values as one day plus whatever the difference was, so you might have to subtract 1 from totals.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40553884
Try:

,SUM(DATEDIFF(SECOND, '00:00:00.000', CAST([Total Down Time] AS time)))

Edit: Just noticed that Leon had already mentioned this, so please ignore my comment.
0
 

Author Comment

by:SQLSearcher
ID: 40557226
Hello Leon
Some of my date times are grater than 1 day long which is causing me problem with the sum of the time.

What is a UDF?

Regards

SQLSearcher
0
 
LVL 5

Expert Comment

by:Leon Kammer
ID: 40557267
Hi SQLSearcher,

A UDF is a User Defined Function, this is a function that is created to do something (in this case, calculate time in seconds)

I am assuming that the dates are in fact correct, in which case, the time in seconds that SQL server is reporting is correct.

Is there an issue with the data coming back?
Cheers

Leon
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

752 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