日期函数

月份和日的补零方法：Select Right(100+Month(GetDate()),2)或者Right(100+Day(GetDate()),2)

例子：

--新增用户  

SELECT CAST(DATEPART(MONTH,adddate) AS NVARCHAR(2))+'-'+RIGHT(100+DAY(adddate),2),COUNT(*) FROM users.dbo.users 

WHERE adddate>'2010-8-1' AND adddate<'2010-10-1'

GROUP BY CAST(DATEPART(MONTH,adddate) AS NVARCHAR(2))+'-'+RIGHT(100+DAY(adddate),2)

ORDER BY CAST(DATEPART(MONTH,adddate) AS NVARCHAR(2))+'-'+RIGHT(100+DAY(adddate),2)

 

--访问量和点击量

SELECT DATEPART(day,LastLoginTime),

(SELECT COUNT(DISTINCT UserID) FROM USERS.DBO.UserLoginLog WHERE LoginTime>'2010-9-1'

AND LoginTime<'2010-10-1'

AND DATEPART(day,LastLoginTime)=DATEPART(day,LoginTime))

,

(SELECT COUNT(*) FROM USERS.DBO.UserLoginLog WHERE LoginTime>'2010-9-1'

AND LoginTime<'2010-10-1'

AND DATEPART(day,LastLoginTime)=DATEPART(day,LoginTime))

 

FROM users.dbo.users 

WHERE LastLoginTime>'2010-9-1' AND LastLoginTime<'2010-10-1'

GROUP BY DATEPART(day,LastLoginTime)

ORDER BY DATEPART(day,LastLoginTime)