CODEWITHSUNDEEP
sqlsql-servert-sql
Hosea146
Hosea146

Reputation: 7702

Rounding a datetime value down to the nearest half hour

I have a requirement to round a datetime2 value down to the nearest half hour. For example '10/17/2013 12:10:00.123' would round down to '10/17/2013 12:00:00.0' And '10/17/2013 12:34:17.123' would round down to 10/17/2013 12:30:00.0'. My first thought was to create a UDF which would break the date and time apart and do it that way. However, I'm wondering if something like this can be done in a single T-SQL statement?

I'm using SQL Server 2012 and the data type of the column is a dateTime2 (which cannot be converted to a float!!)

Upvotes: 13

Views: 27024

Answers (7)

Salman Arshad
Salman Arshad

Reputation: 272006

You can use DATETIME2FROMPARTS to reconstruct the date. To round the minutes down to 30 minute intervals use the formula minutes intdiv 30 * 30

SELECT
    dt2,
    DATETIME2FROMPARTS(
        DATEPART(year, dt2),
        DATEPART(month, dt2),
        DATEPART(day, dt2),
        DATEPART(hour, dt2),
        DATEPART(minute, dt2) / 30 * 30,
        0,
        0,
        0
    )
FROM (VALUES
    -- generic datetime2
    (SYSDATETIME()),
    -- 30 minute boundary
    ('2001-01-01 00:29:59.9999999'),
    ('2001-01-01 00:30:00.0000000'),
    ('2001-01-01 00:30:00.0000001'),
    -- min and max date
    ('0001-01-01 00:00:00.0000000'),
    ('9999-12-31 23:59:59.9999999')
) AS v(dt2)

Upvotes: 0

Decula
Decula

Reputation: 508

@Twinkles's answer works well in SQL server to round to closest half an hour.

However, in development, strongly recommend use FLOOR to round to last half an hour.

SELECT CONVERT(datetime, FLOOR(CAST([columnname] AS float) * 48.0)/48.0) FROM [tableName]

Upvotes: 0

acatala
acatala

Reputation: 56

Here is a slightly different approach that I used when I needed to round down to the nearest 5 minute interval. There is probably a way to simplify this further, but at least this got me what I needed.

DECLARE @now datetime = GETDATE()

SELECT @now as cur_datetime, DATEADD(MINUTE, -(DATEDIFF(MINUTE,DATEADD(HOUR,DATEDIFF(HOUR,0,@now), 0),DATEADD(MINUTE,DATEDIFF(MINUTE,0,@now), 0)) % 5), DATEADD(MINUTE,DATEDIFF(MINUTE,0,@now), 0)) as round_down_to_nearest_5_minute_mark

Upvotes: 0

Steve Kass
Steve Kass

Reputation: 7184

Here is one way to do it:

update t set
  d = dateadd(minute,datediff(minute,'19000101',d)/30*30,'19000101');

Upvotes: 1

BlueMonkMN
BlueMonkMN

Reputation: 25601

select cast(floor(cast(
    cast('10/17/2013 12:34:00' as datetime) 
as float(53)) * 48) / 48 as datetime)

EDIT

Works better if you use smalldatetime to avoid the extra precision

select cast(floor(cast(
    cast('2012-01-02 11:33:14.097' as smalldatetime) 
as float(53)) * 48) / 48 as smalldatetime)

Upvotes: 0

Andrew
Andrew

Reputation: 5277

How about this

declare @d datetime = '2013-05-06 12:29.123'
select 
case 
    when datepart(minute, @d) < 30 then cast(dateadd(minute, -datepart(minute,@d)-datepart(second,@d), @d) as smalldatetime)
    when datepart(minute, @d) >= 30 then cast(dateadd(minute, -datepart(minute,@d)-datepart(second,@d)+30, @d) as smalldatetime)
end

Upvotes: 1

Twinkles
Twinkles

Reputation: 1994

The answer by Ian is good, but it contains an unnecessary conversion. I suggest

SELECT CONVERT(smalldatetime, ROUND(CAST([columnname] AS float) * 48.0,0,1)/48.0) FROM [tableName]

If you want to round to the nearest half-hour instead of always rounding down, use

SELECT CONVERT(smalldatetime, ROUND(CAST([columnname] AS float) * 48.0,0)/48.0) FROM [tableName]

Upvotes: 17

Related Questions