As follows:
drop table Tempdate
DECLARE @sdate datetime
DECLARE @endtime datetime
Set @sdate = ' 2000/1/1 '
Set @endtime = ' 2015/1/31 '
Select DATEADD (Dd,row_number () over (order by "), @sdate) as date into Tempdate from master. spt_values WHERE type = ' P ' and DATEADD (Dd,number, @sdate) <= DATEADD (mm,0, @endtime)
SELECT * FROM Master. Spt_values where type= ' P '
Select A.date,count=case when B.count was null then 0 else B.count end from Tempdate a
Left JOIN (
Select ' 2015/1/1 ' as data, ' 1 ' as Count
UNION ALL
Select ' 2015/1/2 ' as data, ' 2 ' as Count
UNION ALL
Select ' 2015/1/3 ' as Data, ' 3 ' as Count
UNION ALL
Select ' 2015/1/4 ' as data, ' 4 ' as Count
UNION ALL
Select ' 2015/1/5 ' as data, ' 5 ' as Count ' B on a.date=b.data
This article is from the "stubborn 蝸 cow" blog, please be sure to keep this source http://5090844.blog.51cto.com/5080844/1606189
SQL Fetch daily Date