First get the values as dates:
DECLARE @input_string varchar(8)
SET @input_string = '180897'
DECLARE @new_string varchar(10)
SET @new_string = LEFT(@input_string, 2) + '/' + SUBSTRING(@input_string, 3,2) + '/' + RIGHT(@input_string, 2)
DECLARE @new_date DATETIME
SET @new_date = CONVERT(DATETIME, @new_string, 3)
- here is
the link[
^].
Then you re-cast back to string you want:
SET @new_string = DAY(@new_date) + '/' + LEFT(DATENAME(MONTH,@new_date),3) +'/' + YEAR(@new_date)
Slight simplyfication is to save varchar values while parsing instead of creating datetime object and use DATENAME LEFT(MONTH, middle_part_from_above), 3)
If this helps please take time to accept the solution.