I have a data field Date in the format of yyyyMMdd string (varchar). How do I replace everything before a certain date, say 20180201, to certain month, say March 2018!? Only the year and the month are changed, keep the day.
Example: A string 20160913 will be changed to 20180313, but 20180224 will be kept as it was, because it was after 20180201.
Convert that string to a date to search for the greater than a date, and use substring to extract the day part to build the new date string.
declare @NewYear varchar(4) = '2018'
declare @NewMonth varchar(2) = '03'
declare @PivotDate date = '2018-02-01'
update myTable set MyField = @NewYear + @NewMonth + substring(MyField, 6, 2)
where convert(date, MyField, 112) > @PivotDate
I would recommend doing this as:
declare @NewYear varchar(4) = '2018';
declare @NewMonth varchar(2) = '03';
declare @PivotDate date = '2018-02-01';
update myTable
set MyField = @NewYear + @NewMonth + right(MyField, 2)
where myfield < convert(varchar(255), @PivotDate, 112);
The important part of this solution is the where clause. In particular, there is no function call on myfield which allows an index to be used. An expression that allows an index is called sargable.
I would recommend to doing this, because if @NewMonth < February, then it can be a problem.
declare @NewYear varchar(4) = '2018'
declare @NewMonth varchar(2) = '02'
declare @PivotDate date = '2018-02-01'
update myTable set MyField = DATEADD(day,(right(MyField, 2)-1),cast((@NewYear + @NewMonth + '01') as date))
where convert(date, MyField, 112) > @PivotDate
Thank You!
