rounding time

Question

Hello All,
I am using this script to round up time to the nearest 15 minutes

select shift_in, DATEADD (minute, (15 - DATEPART(minute,shift_in) % 15), shift_in) from tc_att;

the problem I have is that 2021-01-11 12:30:00.000 is round up to 2021-01-11 12:45:00.000
I would like it to stay as 2021-01-11 12:30:00.000

here is my table

CREATE TABLE [dbo].[tc_att](
[local_id] [int] IDENTITY(1,1) NOT NULL,
[pc_user] char NOT NULL,
[emp_group] char NOT NULL,
[employee] char NOT NULL,
[actual_date] [date] NOT NULL,
[shift] char NOT NULL CONSTRAINT [DF_tc_att_shift] DEFAULT (' '),
[gss_date_in] char NULL,
[gss_date_out] char NULL,
[gss_bal_as_date] char NULL,
[umc_date_in] [date] NULL,
[umc_date_out] [date] NULL,
[gss_time_in] char NULL,
[gss_time_out] char NULL,
[umc_bal_as_date] [date] NULL,
[shift_in] [datetime] NULL,
[shift_out] [datetime] NULL,
[week_id] [int] NOT NULL CONSTRAINT [DF_tc_att_week_id] DEFAULT ((0)),

Please help,
Thank you all

Answer 1

Check if this example can be used in your case:

declare @shift_in datetime = '2021-01-11 12:30:00.000'

select @shift_in, dateadd(minute, cast((datediff(minute, cast(cast(@shift_in as date) as datetime), @shift_in) + 14) / 15 as int) * 15, cast(cast(@shift_in as date) as datetime))

Answer 2

DECLARE @tc_att TABLE ([shift_in] [datetime] NULL,[shift_out] [datetime] NULL)

INSERT INTO @tc_att VALUES
('2021-01-11 15:11:00','2021-01-11 15:29:00'),
('2021-01-11 12:30:00','2021-01-11 12:45:00')

SELECT 
    DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( SECOND, (15*60)-1, shift_in ) ) / 15 ) * 15, 0 ) as RoundedShiftIn,
    DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( SECOND,(15*60)-1, shift_out ) ) / 15 ) * 15, 0 ) as RoundedShiftOut

FROM @tc_att