Skip to content
Advertisement

Unable to replace the hour in datetime using SQL

Unable to replace the hour part in date-time. The change is reflecting in both hour & year. Please help in rectifying the code.

DECLARE @p varchar(100) = CONVERT(varchar(100), GETDATE(), 21) 

SET @p = REPLACE(@p, SUBSTRING(@p, 12, 2), '11')
PRINT @p
  • Expected output = 2021-02-13 11:35:32.337
  • Actual output = 1121-02-13 11:39:11.347
DECLARE @q varchar(100) 
SET @q = REPLACE(CONVERT(varchar(100), GETDATE(), 121), SUBSTRING(CONVERT(varchar(100), GETDATE(), 121), 12, 2), 22)
PRINT @q
  • Expected output = 2021-02-13 22:35:32.337
  • Actual output = 2221-02-13 22:38:13.223

Advertisement

Answer

REPLACE(@p, SUBSTRING(@p, 12, 2), '11') will replace all occurrences of SUBSTRING(@p, 12, 2) in @p by '11'.

It happens that SUBSTRING(@p, 12, 2) returns '20' (or it could be any other value for the hour), so all occurrences of '20' in @p are replaced by '11'.
This is why for the year 2021 you get 1121.

Instead of REPLACE() use string concatenation:

set @p = SUBSTRING(@p,1,11) + '11' + SUBSTRING(@p,14,LEN(@p))

or the function STUFF():

set @p = STUFF(@p,12,2,'11')

See a simplified demo.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement