--创建表
create table NCC_Infos(
INFO_ID int,
Info_MangerAddTime datetime
)
insert into NCC_Infos(INFO_ID,Info_MangerAddTime) values(1,'2014-05-10 10:25:15.000')
insert into NCC_Infos(INFO_ID,Info_MangerAddTime) values(2,'2014-04-13 10:26:21.000')
insert into NCC_Infos(INFO_ID,Info_MangerAddTime) values(3,'2014-06-11 10:27:31.000')
insert into NCC_Infos(INFO_ID,Info_MangerAddTime) values(4,'2014-06-30 11:05:08.000')
select * from NCC_Infos
--要得到如下结果：即给月份加3，日期都变成19，注意：不要改变原表数据，只要查询出来的
INFO_ID Info_MangerAddTime
1 2014-08-10 10:25:15.000
2 2014-07-13 10:26:21.000
3 2014-09-11 10:27:31.000
4 2014-09-30 11:05:08.000
------解决方案--------------------
--create table NCC_Infos(
--INFO_ID int,
--Info_MangerAddTime datetime
--)
--insert into NCC_Infos(INFO_ID,Info_MangerAddTime) values(1,'2014-05-10 10:25:15.000')
--insert into NCC_Infos(INFO_ID,Info_MangerAddTime) values(2,'2014-04-13 10:26:21.000')
--insert into NCC_Infos(INFO_ID,Info_MangerAddTime) values(3,'2014-06-11 10:27:31.000')
--insert into NCC_Infos(INFO_ID,Info_MangerAddTime) values(4,'2014-06-30 11:05:08.000')
select INFO_ID,SUBSTRING(CONVERT(VARCHAR(30),DATEADD(MONTH,3,Info_MangerAddTime),121),1,8)+'19'+' '+CONVERT(VARCHAR(20),CONVERT(TIME,(CONVERT(VARCHAR(30),DATEADD(MONTH,3,Info_MangerAddTime),121))))Info_MangerAddTime
from NCC_Infos
/*
INFO_ID Info_MangerAddTime
----------- -------------------------------
1 2014-08-19 10:25:15.0000000
2 2014-07-19 10:26:21.0000000
3 2014-09-19 10:27:31.0000000
4 2014-09-19 11:05:08.0000000
*/
------解决方案--------------------
-- 月份加3
select INFO_ID,
dateadd(m,3,Info_MangerAddTime) 'Info_MangerAddTime'
from NCC_Infos
/*
INFO_ID Info_MangerAddTime
----------- -----------------------
1 2014-08-10 10:25:15.000
2 2014-07-13 10:26:21.000
3 2014-09-11 10:27:31.000
4 2014-09-30 11:05:08.000
(4 row(s) affected)
*/
-- 日期都变成19
select INFO_ID,
cast(stuff(convert(varchar,Info_MangerAddTime,120),9,2,'19') as datetime) 'Info_MangerAddTime'
from NCC_Infos
/*
INFO_ID Info_MangerAddTime
----------- -----------------------
1 2014-05-19 10:25:15.000
2 2014-04-19 10:26:21.000
3 2014-06-19 10:27:31.000
4 2014-06-19 11:05:08.000
(4 row(s) affected)
*/
-- 月份加3 日期都变成19
select INFO_ID,
cast(stuff(convert(varchar,dateadd(m,3,Info_MangerAddTime),120),9,2,'19')
as datetime) 'Info_MangerAddTime'
from NCC_Infos
/*
INFO_ID Info_MangerAddTime
----------- -----------------------
1 2014-08-19 10:25:15.000
2 2014-07-19 10:26:21.000
3 2014-09-19 10:27:31.000
4 2014-09-19 11:05:08.000
(4 row(s) affected)
*/
------解决方案--------------------
SELECT INFO_ID ,
DATEADD(mm, 3, DATEADD(d, 19 - DAY(Info_MangerAddTime), Info_MangerAddTime)) AS Info_MangerAddTime
FROM NCC_Infos
/*
INFO_ID Info_MangerAddTime
----------- -----------------------
1 2014-08-19 10:25:15.000
2 2014-07-19 10:26:21.000
3 2014-09-19 10:27:31.000
4 2014-09-19 11:05:08.000
*/
------解决方案--------------------