Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
doyl0057
Starting Member
1 Post |
 Posted - 2008-06-05 : 14:28:16
|
| I am trying to output the difference in minutes between 2 times. The sql statement works for the most part but if I compare a time before midnight and a time after midnight, the query returns a negative number. Here is my SQL statement:SELECT dbo.fdc_trips.enrdate,dbo.FDC_Trips.enrtime, dbo.fdc_trips.atsdate,dbo.FDC_Trips.atstime, DATEDIFF(n, dbo.FDC_Trips.enrtime, dbo.FDC_Trips.atstime) AS TimeDiffFROM dbo.FDC_Trips For example: if enrtime = 23:57:00 and atstime = 00:04:00, the DATEDIFF formula returns -1433, and really it should be 7.How can I get this query to work with times before and after midnight? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-06-05 : 14:32:23
|
| what are your date parts of the datetime?_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-05 : 14:33:20
|
| Since you are providing the time alone, It considers the date part to be default value (1 jan 1900). So enrtime =01/01/1900 23:57:00 and atstime =01/01/1900 00:04:00, and it thinks enrtime is after atstime so it gives negative result as - (minutes between 00:04 to 23:57 which will be -(23 * 60 + 53) =-1433. For calaculating the value correctly you need to consider date part as well. |
|
|
|
|
|
|
DATEDIFF returns negative number