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.