CODEWITHSUNDEEP
sqlsql-server-2008
Stack User
Stack User

Reputation: 1398

Datetime difference casting

DateTime1: 2010-11-23 15:30:00.000

DateTime2: 2010-11-23 18:30:00.000

DateTime2 - DateTime1 should be 03:00:00

but when I use below query

SELECT (EndTime - StartTime) as Hour From [Day]

it returns 

1900-01-01 03:00:00.000

how can I get it like 03:00?

Upvotes: 0

Views: 60

Answers (2)

Stack User
Stack User

Reputation: 1398

Datediff function is suitable for this operation.

SELECT DATEDIFF ( hour , StartTime, EndTime ) as hour From [Day]

and have a look http://msdn.microsoft.com/en-us/library/ms189794.aspx

Upvotes: 0

GarethD
GarethD

Reputation: 69769

As you have said, DATEDIFF is suitable. Formatting is probably best left to the client side, however the below will work as you have requested

DECLARE @Start DATETIME = '20120716 09:00',
        @End DATETIME = '20120717 12:00'

SELECT  CASE WHEN DATEDIFF(HOUR, @Start, @End) < 10 THEN '0' ELSE '' END + CAST(DATEDIFF(HOUR, @Start, @End) AS VARCHAR) + ':' + 
        RIGHT('0' + CAST(DATEDIFF(MINUTE, @Start, @End) - (DATEDIFF(HOUR, @Start, @End) * 60) AS VARCHAR), 2) [Difference]

Upvotes: 1

Related Questions