Solved

Problem with datediff in SQL not calculating correctly

Posted on 2014-03-11
3
392 Views
Last Modified: 2014-05-05
SQL Code CAST(DATEDIFF(ss, StartDate, StopDate) AS Numeric(4 , 0))

Result

2014-01-26 15:01:00.000      2014-01-26 15:01:20.000      60
2014-01-26 15:04:50.000      2014-01-26 15:05:00.000      20

Expected

2014-01-26 15:01:00.000      2014-01-26 15:01:20.000      20
2014-01-26 15:04:50.000      2014-01-26 15:05:00.000      10

All ideas most welcome
0
Comment
Question by:SweetingA
3 Comments
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 500 total points
ID: 39922132
Thats pretty odd, what happens when you hardcode it i.e. run the query:

select datediff(ss, '2014-01-26 15:01:00.000','2014-01-26 15:01:20.000')

Open in new window


Also could you post your actual query as

select StartDate, EndDate, datediff(ss, StartDate, EndDate) from MyTableName
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 39922135
My test code produces the desired results, so I'm not sure why you would be seeing a different result.  What are the column types of the start and stop date(s)?  Note that it would only take a few days difference between the two dates to overflow the numeric(4,0) field.

declare @StartDate datetime , @StopDate datetime

select @StartDate = '2014-01-26 15:01:00.000' , @StopDate = '2014-01-26 15:01:20.000'
select CAST(DATEDIFF(ss, @StartDate, @StopDate) AS Numeric(4,0))

select @StartDate = '2014-01-26 15:04:50.000' , @StopDate = '2014-01-26 15:05:00.000'
select CAST(DATEDIFF(ss, @StartDate, @StopDate) AS Numeric(4,0))

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39927975
Hmm.  It looks like it had to be that the real difference was, say, 10020, which was then truncated to become 0020 in the final result.

But that would normally cause either an error or a NULL value, depending on your SQL settings.

Is it possible that the actual code and computations might have allowed the value to be truncated that way without causing an error?
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

839 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