SQL ²é¿´±¾ÖܵÄÐÇÆÚÒ»ºÍÐÇÆÚÌì

--SQL ²é¿´±¾ÖܵÄÐÇÆÚÒ»ºÍÐÇÆÚÌì
/*
ϵͳµÄʱ¼äĬÈÏ°ÑÐÇÆÚÌì×÷Ϊһ¸öÐÇÆڵĵÚÒ»Ì죬µ«ÊÇÎÒÃÇÖйúÏ°¹ß°ÑÐÇÆÚÒ»×÷Ϊһ¸öÐÇÆڵĵÚÒ»Ì죬Òò´ËÈç¹ûµ±ÌìÊÇÐÇÆÚÌìʱÐèÒªÅжÏÒ»ÏÂ
*/
DECLARE @MondayTime varchar(10)
DECLARE @SundayTime varchar(10)
IF(datepart(weekday,getdate())-2 < 0)
BEGIN
 SET @MondayTime = convert(varchar(10),dateadd(dd,-1,getdate())-(datepart(weekday,dateadd(dd,-1,getdate()))-2),120)
 SET @SundayTime = convert(varchar(10),dateadd(dd,-1,getdate())+(8-datepart(weekday,dateadd(dd,-1,getdate()))),120)
END
ELSE
 BEGIN
  SET @MondayTime =convert(varchar(10),getdate()-(datepart(weekday,getdate())-2),120)
  SET @SundayTime =convert(varchar(10),getdate()+(8-datepart(weekday,getdate())),120)
 END
 SELECT @MondayTime as 'µÚÒ»Ìì(ÖÜÒ»)', @SundayTime as '×îºóÒ»Ìì(ÖÜÈÕ)'
 
--Ò²¿ÉÒÔÉèÖÃÿÖܵĵÚÒ»Ìì
ÉèÖÃÐÇÆÚһΪһÖܵĵÚÒ»Ìì
SET DATEFIRST ÉèÖÃÒ»ÖܵĵÚÒ»ÌìÊÇÐÇÆÚ¼¸£¬¸ÃÉèÖöÔËùÓÐÓû§¾ùÓÐЧ£¬³ý·ÇÔÙ´ÎÐ޸ģ¬·ñÔò¸ÃÉèÖý«Ò»Ö±±£Áô¡£
SET DATEFIRSTµÄ¾ßÌåÓï·¨ÈçÏ£º
SET DATEFIRST { number | @number_var }
ÆäÖвÎÊýnumber | @number_varÊÇÒ»¸öÕûÊý£¬±íʾһÖܵĵÚÒ»Ìì¡£ÔÊÐíµÄÖµÊÇ1¡«7£¬1±íʾһÖܵĵÚÒ»ÌìÊÇÐÇÆÚÒ»£¬7±íʾһÖܵĵÚÒ»Ìì¶ÔӦΪÐÇÆÚÈÕ¡£numberµÄĬÈÏÖµÊÇ7£¨¼´Ò»ÖܵĵÚÒ»ÌìΪÐÇÆÚÈÕ£©¡£¿ÉÒÔͨ¹ý²éѯϵͳ±äÁ¿@@DATEFIRST»ñÈ¡µ±Ç°µÄSET DATEFIRSTÉèÖÃÖµ¡£
DATENAME(dw,date)¼°DATEPART(dw,date)µÄ½á¹û»áÊܵ½SET DATEFIRSTÉèÖÃÖµµÄÓ°Ïì¡£
SET DATEFIRST 1
--Õâʱ²éÕÒ±¾ÖܵÄÊý¾Ý¾Í·½±ã¶àÁË
SELECT * from T_news WHERE (DATEPART(wk, addtime) = DATEPART(wk, GETDATE())) AND (DATEPART(yy, addtime) = DATEPART(yy, GETDATE()))
ÁíÍ⣺
Ò»Ìì "select * from T_news where datediff(day,addtime,getdate())=0";
ÈýÌì "select * from T_news where datediff(day,addtime,getdate())<= 2 and datediff(day,addtime,getdate())>= 0";
Ò»ÔÂ "SELECT * from T_news WHERE (DATEPART(yy, addtime) = DATEPART(yy, GETDATE())) AND (DATEPART(mm, addtime) = DATEPART(mm, GETDATE()))";
Ò»¼¾¶È "select * from t_news where DATEPART(qq, addtime) = DATEPART(qq, GETDATE()) and DATEPART(yy, a