Solved

SQL Server and sum of Time

Posted on 2015-01-16
6
247 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 69

Expert Comment

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now