Solved

SQL Server and sum of Time

Posted on 2015-01-16
6
249 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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

770 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