0

From a stored procedure in T-SQL, I want to get the time from the system. So, if for example, the time from the system is 12:29, the function should to return 12:00, if the time of the system is 12:31 the function should to return 13:00.

How can I do this ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bircastri
  • 2,169
  • 13
  • 50
  • 119
  • 1
    Possible duplicate of [T-SQL datetime rounded to nearest minute and nearest hours with using functions](https://stackoverflow.com/questions/6666866/t-sql-datetime-rounded-to-nearest-minute-and-nearest-hours-with-using-functions) – Alex K. Jul 11 '17 at 11:16

1 Answers1

1

You could use DATEADD function:

DECLARE @time TIME = '12:31:00'

SELECT CAST(DATEADD(Hour,IIF(DATEPART(mi, @time)<=30,0,1),DATEADD(Hour, DATEDIFF(Hour, 0, @time), 0)) AS TIME);

Rextester Demo

How it works:

  1. Truncate time to nearest hour
  2. Add 1 hour if minutes > 30 (or change logic if you need to handle seconds as well)

EDIT:

Prior to SQL Server 2012 you could use CASE instead of IIF

DECLARE @time TIME = '12:31:00'

SELECT CAST(DATEADD(Hour,CASE WHEN DATEPART(mi, @time)<=30 THEN 0 ELSE 1 END,DATEADD(Hour, DATEDIFF(Hour, 0, @time), 0)) AS TIME);

Rextester Demo 2

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275