Solved

SQL Server and sum of Time

Posted on 2015-01-16
6
248 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard 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.
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.

863 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

26 Experts available now in Live!

Get 1:1 Help Now