How to substratc minus 4 seconds from date column

Raj0125 391 Reputation points
2023-02-24T11:07:50.6766667+00:00

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

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.
9,177 questions
No comments
{count} votes

Accepted answer
  1. Viorel 89,396 Reputation points
    2023-02-24T12:24:50.2933333+00:00

    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 27,306 Reputation points
    2023-02-24T11:22:49.4833333+00:00

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

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