æ£ç´¢æ¥ææå¨å¨çä¸å¨å天æ¥ææ¹æ³
ä¸ãç¨å°çå½æ°ædatepart(),dateadd()
1ãdatepart()å½æ°ï¼è¿å代表æå®æ¥æçæå®æ¥æé¨åçæ´æ°ã
è¯æ³ï¼DATEPART ( datepart ,date )
åæ°ï¼datepart
æ¯æå®åºè¿åçæ¥æé¨åçåæ°ãåæ°å¦ä¸
2ãDATEADD() å½æ°å¨æ¥æä¸æ·»å æåå»æå®çæ¶é´é´éã
è¯æ³ï¼DATEADD(datepart,number,date)
date åæ°æ¯åæ³çæ¥æ表达å¼ãnumber æ¯æ¨å¸ææ·»å çé´éæ°ï¼å¯¹äºæªæ¥çæ¶é´ï¼æ¤æ°æ¯æ£æ°ï¼å¯¹äºè¿å»çæ¶é´ï¼æ¤æ°æ¯è´æ°ã
datepart åæ°å¯ä»¥æ¯ä¸åçå¼ï¼
äºã以系ç»å½åæ¶é´ä¸ºä¾ï¼æ£ç´¢ä¸å¨å天æ¶é´çè¯å¥å¦ä¸ï¼
1ãDATEPART(weekday,getdate())è¿åçæ¯æ´åæ°å¼1-7ï¼åå«ä»£è¡¨å¨æ¥ãå¨ä¸å°å¨å
2ãè¯å¥åå«è·åå¨æ¥å°å¨å
çæ¥ææ¶é´ï¼ç¶åç¨union è¿è¡æ£ç´¢ç»æè¿æ¥ã
3ãå·²è·åå¨æ¥æ¶é´ä¸ºä¾ï¼
DATEPART(weekday,getdate()) è¿å1ï¼å³å½åæ¥æå°±æ¯å¨æ¥ï¼é£ä¹è¾åºå½åæ¶é´getdate()ï¼
DATEPART(weekday,getdate()) è¿å2ï¼å³åæ¥ææ¯å¨ä¸ï¼é£ä¹å¨æ¥æ¯åä¸å¤©ï¼ä½¿ç¨å½æ°è·ååä¸å¤©çæ¥æï¼dateadd(dd,-1,getdate())
以æ¤ç±»æ¨å°±è·åäºæ¥ææå¨å¨çå¨æ¥æ¥ææ¶é´ã
select
case when DATEPART(weekday,getdate())=1 then getdate()
when DATEPART(weekday,getdate())=2 then dateadd(dd,-1,getdate())
when DATEPART(weekday,getdate())=3 then dateadd(dd,-2,getdate())
when DATEPART(weekday,getdate())=4 then dateadd(dd,-3,getdate())
when DATEPART(weekday,getdate())=5 then dateadd(dd,-4,getdate())
when DATEPART(weekday,getdate())=6 then dateadd(dd,-5,getdate())
when DATEPART(weekday,getdate())=7 then dateadd(dd,-6,getdate()) end as 'æ¥æ','å¨æ¥' union
select
case when DATEPART(weekday,getdate())=1 then dateadd(dd,1,getdate())
when DATEPART(weekday,getdate())=2 then dateadd(dd,0,getdate())
when DATEPART(weekday,getdate())=3 then dateadd(dd,-1,getdate())
when DATEPART(weekday,getdate())=4 then dateadd(dd,-2,getdate())
when DATEPART(weekday,getdate())=5 then dateadd(dd,-3,getdate())
when DATEPART(weekday,getdate())=6 then dateadd(dd,-4,getdate())
when DATEPART(weekday,getdate())=7 then dateadd(dd,-5,getdate()) end as 'æ¥æ','å¨ä¸' union
select
case when DATEPART(weekday,getdate())=1 then dateadd(dd,2,getdate())
when DATEPART(weekday,getdate())=2 then dateadd(dd,1,getdate())
when DATEPART(weekday,getdate())=3 then dateadd(dd,0,getdate())
when DATEPART(weekday,getdate())=4 then dateadd(dd,-1,getdate())
when DATEPART(weekday,getdate())=5 then dateadd(dd,-2,getdate())
when DATEPART(weekday,getdate())=6 then dateadd(dd,-3,getdate())
when DATEPART(weekday,getdate())=7 then dateadd(dd,-4,getdate()) end as 'æ¥æ','å¨äº' union
select
case when DATEPART(weekday,getdate())=1 then dateadd(dd,3,getdate())
when DATEPART(weekday,getdate())=2 then dateadd(dd,2,getdate())
when DATEPART(weekday,getdate())=3 then dateadd(dd,1,getdate())
when DATEPART(weekday,getdate())=4 then dateadd(dd,0,getdate())
when DATEPART(weekday,getdate())=5 then dateadd(dd,-1,getdate())
when DATEPART(weekday,getdate())=6 then dateadd(dd,-2,getdate())
when DATEPART(weekday,getdate())=7 then dateadd(dd,-3,getdate()) end as 'æ¥æ','å¨ä¸' union
select
case when DATEPART(weekday,getdate())=1 then dateadd(dd,4,getdate())
when DATEPART(weekday,getdate())=2 then dateadd(dd,3,getdate())
when DATEPART(weekday,getdate())=3 then dateadd(dd,2,getdate())
when DATEPART(weekday,getdate())=4 then dateadd(dd,1,getdate())
when DATEPART(weekday,getdate())=5 then dateadd(dd,0,getdate())
when DATEPART(weekday,getdate())=6 then dateadd(dd,-1,getdate())
when DATEPART(weekday,getdate())=7 then dateadd(dd,-2,getdate()) end as 'æ¥æ','å¨å' union
select
case when DATEPART(weekday,getdate())=1 then dateadd(dd,5,getdate())
when DATEPART(weekday,getdate())=2 then dateadd(dd,4,getdate())
when DATEPART(weekday,getdate())=3 then dateadd(dd,3,getdate())
when DATEPART(weekday,getdate())=4 then dateadd(dd,2,getdate())
when DATEPART(weekday,getdate())=5 then dateadd(dd,1,getdate())
when DATEPART(weekday,getdate())=6 then dateadd(dd,0,getdate())
when DATEPART(weekday,getdate())=7 then dateadd(dd,-1,getdate()) end as 'æ¥æ','å¨äº' union
select
case when DATEPART(weekday,getdate())=1 then dateadd(dd,6,getdate())
when DATEPART(weekday,getdate())=2 then dateadd(dd,5,getdate())
when DATEPART(weekday,getdate())=3 then dateadd(dd,4,getdate())
when DATEPART(weekday,getdate())=4 then dateadd(dd,3,getdate())
when DATEPART(weekday,getdate())=5 then dateadd(dd,2,getdate())
when DATEPART(weekday,getdate())=6 then dateadd(dd,1,getdate())
when DATEPART(weekday,getdate())=7 then dateadd(dd,0,getdate()) end as 'æ¥æ','å¨å
'
ä¸ãæ§è¡ç»æ