用datepart函数
select datepart(YY,GETDATE())--年
,DATEPART(MM,GETDATE()),--月
GETDATE()--日
/*结果
2013 12 2013-12-27 11:08:39.103
*/
select datepart(YY,GETDATE())--年
,DATEPART(MM,GETDATE()),--月
GETDATE()--日
/*结果
2013 12 2013-12-27 11:08:39.103
*/
调试欢乐多
,DATEPART(MM,GETDATE())--月
,datepart(dd,GETDATE())--日
,getdate()--整体
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 CASE WHEN DATEPART(MM,GETDATE()) =12 AND datepart(dd,GETDATE())>=26 THEN datepart(year,GETDATE())+1 ELSE datepart(Year,GETDATE()) END --年
,case datepart(dd,GETDATE()) WHEN 26 THEN DATEPART(MM,DATEADD(mm,1,GETDATE())) ELSE DATEPART(MM,GETDATE()) END --月
, datepart(dd,GETDATE())--日
,getdate()--整体
是不是这样:
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
*/
set @date='2013-11-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
when datepart(dd,@date)>25 then DATEPART(MM,@date)+1
else DATEPART(MM,@date) end,
@date
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
*/
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
DATEPART(year,d) '年',
case when DATEPART(day,d) >=26 and DATEPART(month,d) != 12
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
2013 12 2013-12-26
*/
case when DATEPART(day,d) >=26 and DATEPART(month,d)< 12
then datepart(year,DATEADD(month,1,d)) --加1个月,然后取出月份
else DATEPART(year,d) --直接取出月份
end '年',
case when DATEPART(day,d) >=26 and DATEPART(month,d)< 12
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'union all
select '2013-12-31'
)d