Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 416
  • Last Modified:

Problem with datediff in SQL not calculating correctly

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
SweetingA
Asked:
SweetingA
1 Solution
 
Dale BurrellDirectorCommented:
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
 
knightEknightCommented:
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
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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