How to substratc minus 4 seconds from date column

Question

Hi,

I am using Azure sql need to get minus 4 seconds from the column name called startdate (11/02/2023 06:26:52)

I need out put as 11/02/2023 06:26:48 ( Minus 4 seconds )

Also i need to use with Over Partition by clause for each ID.

Required to change only for 2 nd row only (Below highlighted date Effectivestartdate from first row to minus 4 secs in Effectiveenddate in second record

Please suggest.

Thanks in Advance

Answer 1

Probably the problem can be solved by some joins, but if you prefer OVER, then check this statement:

update t
set t.Effective_End_Date = dateadd(second, -4, d1)
from (
	select *, 
		lag(Effective_Start_Date, 1) over (partition by ID order by Effective_Start_Date desc) d1,
		lag(Effective_Start_Date, 2) over (partition by ID order by Effective_Start_Date desc) d2
	from MyTable
) t
where d1 is not null and d2 is null

Although, the ROW_NUMBER is an attractive function too.

Answer 2

Use the DATEADD function and add -4 seconds, example:

select GETDATE(), DATEADD(second, -4, GETDATE())