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.