Solved

Problem with datediff in SQL not calculating correctly

Posted on 2014-03-11
3
401 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

623 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