34,575
社区成员
发帖
与我相关
我的任务
分享
精简一下
DECLARE @a table(s DATETIME)
insert @a select '2001-1-1'
union all select '2001-1-22'
union all select '2001-1-23'
union all select '2001-1-25'
union all select '2001-2-5'
union all select '2001-2-23'
union all select '2001-12-23'
SELECT convert(varchar(7),dateadd(mm,CASE WHEN datepart(dd,s)>22 THEN 1 ELSE 0 end,s),120)
FROM @a
--result
/*-------
2001-01
2001-01
2001-02
2001-02
2001-02
2001-03
2002-01
(所影响的行数为 7 行)
*/
DECLARE @a table(s DATETIME)
insert @a select '2001-1-1'
union all select '2001-1-22'
union all select '2001-1-23'
union all select '2001-1-25'
union all select '2001-2-5'
union all select '2001-2-23'
union all select '2001-12-23'
SELECT CASE WHEN datepart(dd,s)>22 THEN convert(varchar(7),dateadd(mm,1,s),120)
ELSE convert(varchar(7),s,120) END FROM @a
--result
/*-------
2001-01
2001-01
2001-02
2001-02
2001-02
2001-03
2002-01
(所影响的行数为 7 行)
*/
create table tb(riqi datetime)
insert into tb values('2001-1-1')
insert into tb values('2001-1-22')
insert into tb values('2001-1-23')
insert into tb values('2001-1-25')
insert into tb values('2001-2-5')
insert into tb values('2001-2-23')
insert into tb values('2001-12-23')
go
select case when datepart(dd,riqi) > 22 then year(dateadd(mm,1,riqi)) else year(riqi) end 年 ,
case when datepart(dd,riqi) > 22 then month(dateadd(mm,1,riqi)) else month(riqi) end 月
from tb
drop table tb
/*
年 月
----------- -----------
2001 1
2001 1
2001 2
2001 2
2001 2
2001 3
2002 1
(所影响的行数为 7 行)
*/
select Y=year(日期),M=month(日期) from 日期 where day(日期)<23
select year(getdate()),month(getdate())
----------- -----------
2010 4
(1 行受影响)