SQL取上一个月、前一天、上一年，当天，当前月

当前月

1 SELECT * FROM dbo.tb_SO WHERE MONTH(DocDate) = MONTH(GETDATE())

 

取上一个月

1 SELECT * FROM dbo.tb_SO WHERE DocDate BETWEEN DATEADD(MONTH,-1,DATEADD(DAY,1-DAY(GETDATE()),CONVERT(VARCHAR,GETDATE(),23))) AND
2 DATEADD(MILLISECOND,-2,DATEADD(DAY,1-DAY(GETDATE()),CONVERT(VARCHAR,GETDATE(),23)))

取前一天

1  SELECT * FROM dbo.tb_SO WHERE DocDate BETWEEN  DATEADD ( DAY , - 1 , CONVERT ( VARCHAR , GETDATE (), 23 ))  AND   
2  DATEADD (MILLISECOND, - 2 , CONVERT ( VARCHAR , GETDATE (), 23 ))

取上一年

1  SELECT * FROM  dbo.tb_SO WHERE DocDate
2  BETWEEN   DATEADD ( YEAR , - 1 , DATEADD ( DAY , 1 - DATEPART (dayofyear, GETDATE ()), CONVERT ( VARCHAR , GETDATE (), 23 )))
3  AND   DATEADD (MILLISECOND, - 2 , DATEADD ( DAY , 1 - DATEPART (dayofyear, GETDATE ()), CONVERT ( VARCHAR, GETDATE (), 23 )))

取当天

1 SELECT @LoginCount = COUNT(DISTINCT VIPCode) FROM dbo.sys_AppLogin_Log WHERE CONVERT(VARCHAR,LoginTime,23) = CONVERT(VARCHAR,GETDATE(),23)

 

posted @ 2017-05-11 20:21  奕成软件  阅读(19313)  评论(0)  编辑  收藏  举报