How to substratc minus 4 seconds from date column

Raj0125 511 Reputation points


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

User's image

Please suggest.

Thanks in Advance

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,353 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 114.7K Reputation points

    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.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 43,246 Reputation points

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

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