SQL时间函数应用（时间、季度、旬、月、星期）

效果：

 

declare @beginDate date;

set @beginDate=getdate();

select 

convert(varchar(10),@beginDate,112) as datekey,

year(@beginDate) as NIAN,

datepart(quarter,@beginDate) as JIDU,

case 

when datepart(quarter,@beginDate)=1 then '第一季度'

when datepart(quarter,@beginDate)=2 then '第二季度'

when datepart(quarter,@beginDate)=3 then '第三季度'

else  '第四季度' end as QUCH,

month(@beginDate) as YUE,

case

when month(@beginDate)=1 then '1月'

when month(@beginDate)=2 then '2月'

when month(@beginDate)=3 then '3月'

when month(@beginDate)=4 then '4月'

when month(@beginDate)=5 then '5月'

when month(@beginDate)=6 then '6月'

when month(@beginDate)=7 then '7月'

when month(@beginDate)=8 then '8月'

when month(@beginDate)=9 then '9月'

when month(@beginDate)=10 then '10月'

when month(@beginDate)=11 then '11月'

else '12月' end as YUECH,

case 

when day(@beginDate)<=10 then 1

when day(@beginDate)>20 then 3

else  2 end as XUN,

case 

when day(@beginDate)<=10 then '上旬'

when day(@beginDate)>20 then '下旬'

else  '中旬' end as XUNCH,

datepart(week,@beginDate) as weekEH,

datename(weekday,@beginDate) as weekdayCH,

day(@beginDate) as DayEH,

convert(varchar(10),@beginDate,120) as dateEH