Sql query Monthly result but how?

Question

This query returns me first below table. forexample; 2009-06-01 00:00:00 people enter my system '41' times. i need second table

 declare @date1 smalldatetime, @date2 smalldatetime , @countgap int, @page nvarchar(100)   
select @date1='2009-01-01', @date2='2009-12-30 23:59:59' ,@countgap=1, @page='Tüm Sayfalar'

declare @@date1 smalldatetime,@@date2 smalldatetime , @COUNT INT          
   select @@date1=@date1, @@date2=@date2 , @COUNT=1          
   select @@date1 TARIH , @COUNT SIRA           
  INTO #TMP          
  WHILE @@date1 < @@date2           
BEGIN           
   SELECT  @@date1 = DATEadd(month,@countgap,@@date1) , @COUNT = @COUNT +1          
   INSERT INTO #TMP          
   select @@date1 TARIH , @COUNT SIRA           
END  
  select t1.TARIH, t1.SIRA, VISITINGCOUNT = isnull(t2.TARIH, 0)          
   from #TMP t1          
   left join ( 
    select count(page) as TARIH,             
      datepart(month,Date) as SIRA           
    from scr_StatisticaLog            
    where Date between @date1 and  @date2  and 
         (Page=@page or @page='Tüm Sayfalar') and 
          ProcessType='PageView'       
    GROUP BY datepart(month,Date)
             )t2 on t2.SIRA = t1.SIRA          
    order by t1.SIRA          
  return     

2009-01-01 00:00:00 1 0
2009-02-01 00:00:00 2 0
2009-03-01 00:00:00 3 0
2009-04-01 00:00:00 4 1
2009-05-01 00:00:00 5 1
2009-06-01 00:00:00 6 41
2009-07-01 00:00:00 7 0
2009-08-01 00:00:00 8 0
2009-09-01 00:00:00 9 0
2009-10-01 00:00:00 10 0
2009-11-01 00:00:00 11 0
2009-12-01 00:00:00 12 1
2010-01-01 00:00:00 13 0

2009-January     12 1

Answer 1

what you need is a calendar table with every date in it. after that you just left join to it and get your second table.

EDIT: ahh.. new info from poster kind of invalidates this answer...

Answer 2

Use the DATENAME and DATEPART functions to format your date:

SELECT 
  DATEPART(year,DateColumn) + '-' + DATENAME(month,DateColumn) AS FormattedDate
FROM
  Table

Answer 3

Welbog's answer works.

I'd go a bit further. In MySQL land, I'd just do a left(10) on the date, and group by that. You could then do your whole query in one pass with no temp tables or loops. I think SQL server will let you substring a date, but if not you can do it by building the date as Welbog showed you.