Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • 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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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