日期
2001-1-1
2001-1-22
2001-1-23
2001-1-25
2001-2-5
2001-2-23
2001-12-23如何变成:
年 月
2001 1
2001 1
2001 2
2001 2
2001 2
2001 3
2002 1我的账期截止到每月22日,不是自然月,如果要实现以上结果,请问SQL语句怎么写?谢谢!!!
2001-1-1
2001-1-22
2001-1-23
2001-1-25
2001-2-5
2001-2-23
2001-12-23如何变成:
年 月
2001 1
2001 1
2001 2
2001 2
2001 2
2001 3
2002 1我的账期截止到每月22日,不是自然月,如果要实现以上结果,请问SQL语句怎么写?谢谢!!!
----------- -----------
2010 4(1 行受影响)
select case when datepart(dd,riqi) > 22 then year(datediff(mm,1,日期)) else year(日期) end 年 ,
case when datepart(dd,riqi) > 22 then month(datediff(mm,1,日期)) else month(日期) end 月
from tb
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')
goselect 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 tbdrop table tb/*
年 月
----------- -----------
2001 1
2001 1
2001 2
2001 2
2001 2
2001 3
2002 1(所影响的行数为 7 行)
*/
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 行)
*/
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 行)
*/