CODEWITHSUNDEEP
sql-servert-sqldatediffhour
user2059064
user2059064

Reputation: 163

DATEDIFF Round off cutoff date hours SQL Server

My client has this KTV hour rule all check out above 20 mins will be considered as additional 1 hour.

Example code below: 

  declare @dateStart as datetime, @dateEnd as datetime

  set @dateStart = cast('3/2/2013 8:00:00 PM' as datetime)
  set @dateEnd   = cast('3/2/2013 9:20:02 pm' as datetime)

  SELECT DATEDIFF(hour, @dateStart, @dateEnd)

The code above logically returns 1:20 hours. In SQL server it will return 1 hour. How can I tweak this code to return as 2 hours?

Thanks in advance.

Upvotes: 1

Views: 4564

Answers (3)

Ravi Singh
Ravi Singh

Reputation: 2080

Try this :

  declare @dateStart as datetime, @dateEnd as datetime

  set @dateStart = cast('3/2/2013 8:00:00 PM' as datetime)
  set @dateEnd   = cast('3/2/2013 9:20:02 pm' as datetime)

  SELECT DATEDIFF(hour, @dateStart, @dateEnd) 
+
case 
when datediff(minute,@dateStart, @dateEnd)%60>=20 then 1
else 0
end

To be more precise you can also add seconds here.

Upvotes: 1

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

Add 40 minutes to the elapsed time, and truncate to the hour, as here:

  declare @dateStart as datetime, @dateEnd as datetime

  set @dateStart = cast('3/2/2013 8:00:00 PM' as datetime)
  set @dateEnd   = cast('3/2/2013 9:20:02 pm' as datetime)


  select Elapsed = datediff(hour, @dateStart, dateadd(minute,40,@dateEnd))

returns

Elapsed
-----------
2

Upvotes: 0

Iswanto San
Iswanto San

Reputation: 18569

Use this:

declare @dateStart as datetime, @dateEnd as datetime

set @dateStart = cast('3/2/2013 8:00:00 PM' as datetime)
set @dateEnd   = cast('3/2/2013 9:20:02 pm' as datetime)

SELECT CEILING(CAST( DATEDIFF(minute, @dateStart, @dateEnd)as float) / 60)

More: CEILING

Upvotes: 5

Related Questions