• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

SQL Server and sum of Time

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
SQLSearcher
Asked:
SQLSearcher
1 Solution
 
Leon KammerCommented:
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
 
Leon KammerCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott PletcherSenior DBACommented:
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
 
SQLSearcherAuthor Commented:
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
 
Leon KammerCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now