Get Hours, Minutes in two dates

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get Hours, Minutes in two dates

Author

Topic

janets20
Starting Member

12 Posts

Posted - 2010-09-23 : 10:17:34

Hi,

I am using the below query to get difference beiween two dates in hours and minutes.Minutes part look ok but the hours is incorrect.

select datediff(minute,LastDate,getdate()) as "Minutes", datediff(hour,LastDate,getdate()) as "Hours"from emp.

Regards
Janet

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-23 : 10:21:55

Please show some sample data.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-09-23 : 10:27:32

[code]
select
datediff(minute,0,getdate()-LastDate) as "Minutes",
datediff(hour,0,getdate()-LastDate) as "Hours"
from
emp
[/code]

CODO ERGO SUM

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 10:28:51

datediff doesn't work the way you think it does -- when you compare any date part you get a comparison of only that datepart.

So for example you say


SELECT DATEDIFF(HOUR, '2010-01-01T21:59:00.000', '2010-01-01T22:01:00.000')

Then you get 1 even though only 2 minutes have gone by

Try something like this instead


DECLARE @from DATETIME
SET @from = '2010-01-01T23:34:12.000'

DECLARE @duration INT
SET @duration = DATEDIFF(SS, @from, '2010-02-01T12:12:16.000')

SELECT STUFF(CONVERT(CHAR(8), DATEADD(SS, @duration % 86400, 0), 8), 1, 2, CAST(@duration / 3600 AS VARCHAR(12)))

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-23 : 10:31:31

quote:
Originally posted by Michael Valentine Jones

select
datediff(minute,0,getdate()-LastDate) as "Minutes",
datediff(hour,0,getdate()-LastDate) as "Hours"
from
emp

CODO ERGO SUM

Whats the difference with the original post
except the way of writing query ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-09-23 : 10:54:43

quote:
Originally posted by vaibhavktiwari83

quote:
Originally posted by Michael Valentine Jones

select
datediff(minute,0,getdate()-LastDate) as "Minutes",
datediff(hour,0,getdate()-LastDate) as "Hours"
from
emp

CODO ERGO SUM

Whats the difference with the original post
except the way of writing query ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

It produces the correct answer.


select
	datediff(minute,0,ToDT-FromDT) as "Minutes",
	datediff(hour,0,ToDT-FromDT) as "Hours",
	datediff(minute,FromDT,ToDt) as "Minutes Wrong",
	datediff(hour,FromDT,ToDt) as "Hours Wrong",
	DiffDT = ToDT-FromDT
from
	( -- Test data
	select
		FromDT	= convert(datetime,'20101231 00:59:59:997'),
		ToDT	= convert(datetime,'20101231 02:06:00:000')
	) a

Results:


Minutes     Hours       Minutes Wrong Hours Wrong DiffDT
----------- ----------- ------------- ----------- -----------------------
         66           1            67           2 1900-01-01 01:06:00.003

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources