Solved

Problem with datediff in SQL not calculating correctly

Posted on 2014-03-11
3
382 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:ScottPletcher
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 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

20 Experts available now in Live!

Get 1:1 Help Now