34,591
社区成员
发帖
与我相关
我的任务
分享
WITH MU AS (
SELECT NUMBER,DATEADD(DAY,NUMBER,'2013-1-1') AS DATE
FROM MASTER..SPT_VALUES
WHERE TYPE='P'
)
SELECT TOP 20 YEAR(CASE WHEN DAY(DATE)>25 THEN DATEADD(MONTH,1,DATE) ELSE DATE END) AS YEAR
,MONTH(CASE WHEN DAY(DATE)>25 THEN DATEADD(MONTH,1,DATE) ELSE DATE END) AS MONTH
,DATE AS DAY
FROM MU
WHERE DATEPART(DAY,DATE) BETWEEN 24 AND 31
/*
YEAR MONTH DAY
2013 1 2013-01-24 00:00:00.000
2013 1 2013-01-25 00:00:00.000
2013 2 2013-01-26 00:00:00.000
2013 2 2013-01-27 00:00:00.000
2013 2 2013-01-28 00:00:00.000
2013 2 2013-01-29 00:00:00.000
2013 2 2013-01-30 00:00:00.000
2013 2 2013-01-31 00:00:00.000
2013 2 2013-02-24 00:00:00.000
2013 2 2013-02-25 00:00:00.000
2013 3 2013-02-26 00:00:00.000
2013 3 2013-02-27 00:00:00.000
2013 3 2013-02-28 00:00:00.000
2013 3 2013-03-24 00:00:00.000
2013 3 2013-03-25 00:00:00.000
2013 4 2013-03-26 00:00:00.000
2013 4 2013-03-27 00:00:00.000
2013 4 2013-03-28 00:00:00.000
2013 4 2013-03-29 00:00:00.000
2013 4 2013-03-30 00:00:00.000
*/
select
case when DATEPART(day,d) >=26
then datepart(year,DATEADD(month,1,d)) --加1个月,然后取出月份
else DATEPART(year,d) --直接取出月份
end '年',
case when DATEPART(day,d) >=26
then datepart(month,DATEADD(month,1,d)) --加1个月,然后取出月份
else DATEPART(MONTH,d) --直接取出月份
end '月',
d as '日'
from
(
select '2013-11-24' as d union all
select '2013-11-25' union all
select '2013-11-26' union all
select '2013-12-25' union all
select '2013-12-26'
)d
/*
年 月 日
2013 11 2013-11-24
2013 11 2013-11-25
2013 12 2013-11-26
2013 12 2013-12-25
2014 1 2013-12-26
*/
select d,
case when DATEPART(day,d) >=26
then datepart(month,DATEADD(month,1,d)) --加1个月,然后取出月份
else DATEPART(MONTH,d) --直接取出月份
end 'month'
from
(
select '2013-11-24' as d union all
select '2013-11-25' union all
select '2013-11-26' union all
select '2013-12-25' union all
select '2013-12-26'
)d
/*
d month
2013-11-24 11
2013-11-25 11
2013-11-26 12
2013-12-25 12
2013-12-26 1
*/
select case when datepart(d,mytime)>25 then DATEADD(m,1,mytime) else mytime end from Table_4
declare @date datetime
set @date='2013-12-26'
select case when DATEPART(MM,@date)=12 and datepart(dd,@date)>25 then datepart(YY,@date)+1 else datepart(YY,@date) end,
case when DATEPART(MM,@date)=12 and datepart(dd,@date)>25 then 1 else DATEPART(MM,@date) end,
@date
----------- ----------- -----------------------
2014 1 2013-12-26 00:00:00.000
(1 行受影响)
select datepart(YY,GETDATE())--年
,DATEPART(MM,GETDATE())--月
,datepart(dd,GETDATE())--日
,getdate()--整体
select datepart(YY,GETDATE())--年
,DATEPART(MM,GETDATE()),--月
GETDATE()--日
/*结果
2013 12 2013-12-27 11:08:39.103
*/