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.

  • Expected output = 2021-02-13 11:35:32.337
  • Actual output = 1121-02-13 11:39:11.347
  • 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:

or the function STUFF():

See a simplified demo.

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