SQL替换和更新Datetime字符串“在特定日期之前" [英] SQL Replace and Update a Datetime string "before a certain date"

问题描述

我有一个数据字段 Date ，格式为 yyyyMMdd 字符串(varchar).如何在特定日期(例如20180201)到特定月份(例如2018年3月)之前替换所有内容！?仅更改年份和月份，保留日期.

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.

示例:字符串 20160913 将更改为 20180313 ，但 20180224 将保留原样，因为它在之后> 20180201 .

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

这篇关于SQL替换和更新Datetime字符串“在特定日期之前"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！