sql server修改年日月
这是我数据库时间的格式，请问如何将年月日改成2000-01-01
还有一种情况：如果小时和分钟时23:59就改成2000-01-02
[解决办法]
dateadd(year,100,列)
[解决办法]
case when 日期='1900-01-01' then dateadd else 使用其他规则 end
[解决办法]
create table test(start datetime,[end] datetime)
insert into test values('1900-01-01 10:00:00.000','1900-01-01 15:00:00.000')
insert into test values('1900-01-01 10:00:00.000','1900-01-01 18:00:00.000')
insert into test values('1900-01-01 10:00:00.000','1900-01-01 19:00:00.000')
insert into test values('1900-01-01 09:00:00.000','1900-01-01 23:00:00.000')
insert into test values('1900-01-01 09:00:00.000','1900-01-01 23:59:00.000')
insert into test values('2014-01-09 09:00:00.000','2014-01-09 21:00:00.000')
update test set start=DATEADD(YEAR,100,start) where CONVERT(nvarchar(100),start,23)='1900-01-01'
update test set [end]=DATEADD(YEAR,100,[end]) where CONVERT(nvarchar(100),[end],23)='1900-01-01'
update test set start=DATEADD(MINUTE,1,start) where SUBSTRING( CONVERT(nvarchar(100),start,120),12,5)='23:59'
update test set [end]=DATEADD(MINUTE,1,[end]) where SUBSTRING( CONVERT(nvarchar(100),[end],120),12,5)='23:59'
select * from test
/*
2000-01-01 10:00:00.0002000-01-01 15:00:00.000
2000-01-01 10:00:00.0002000-01-01 18:00:00.000
2000-01-01 10:00:00.0002000-01-01 19:00:00.000
2000-01-01 09:00:00.0002000-01-01 23:00:00.000
2000-01-01 09:00:00.0002000-01-02 00:00:00.000
2014-01-09 09:00:00.0002014-01-09 21:00:00.000
*/
