SQL Server - Convert Daily Range query to Monthly range

Asked By farrukh on 19-Dec-11 08:24 AM
HI All,


How to convert the below query for month range the query works fine in daily range.


SELECT

v2

.START_DATETIME,

ISNULL(SUM(v1.TEST_COLUMN),0) AS Test1,

FROM

Table1 v2 left outer join

Table2 v1 on

CONVERT(DATETIME,CONVERT(VARCHAR,v1.[START_DATETIME],101))

<= CONVERT(DATETIME,CONVERT(VARCHAR,v2.[START_DATETIME],101))

WHERE

 v1.NAME IN('Student1','Student2')

AND v2.NAME IN('Student1','Student2')

AND CONVERT(DATETIME,CONVERT(VARCHAR,v2.[START_DATETIME],101)) > @StartDate

AND CONVERT(DATETIME,CONVERT(VARCHAR,v2.[START_DATETIME],101)) < @EndDate

AND CONVERT(DATETIME,CONVERT(VARCHAR,v1.[START_DATETIME],101)) > @StartDate

group by v2.START_DATETIME


Thanks
hammeed

Riley K replied to farrukh on 19-Dec-11 08:32 AM


To query by month you can do like this

SELECT * FROM `dt_tb` WHERE month(dt) between '02' and '08'


Regards
farrukh replied to Riley K on 19-Dec-11 08:37 AM
HI Riley K ,

Actually  i have to use this monthly query in a stored procedure so how it will be...


Thanks
Hammeed
kalpana aparnathi replied to farrukh on 19-Dec-11 01:53 PM

There are quite a few tricks in here, hope you find it useful

create function dbo.fnGetDateRanges
(
   
@type char(1),
   
@start datetime,
   
@finish datetime
)
returns
@ranges table(start datetime, finish datetime)
as
begin

   
declare @from datetime
   
declare @to datetime
   
set @from = @start

   
if @type = 'd'
   
begin
       
set @to = dateadd(day, 1,
                       
convert
                       
(       datetime,
                                cast
(DatePart(d,@start) as varchar) + '/' + cast(DatePart(m,@start) as varchar) + '/' + cast(DatePart(yy,@start) as varchar),
                               
103
                       
)
               
)
   
end

   
if @type = 'm'
   
begin
       
set @to = dateadd(month, 1,
               
convert
               
(      
                        datetime
,
                       
'1/' + cast(DatePart(m,@start) as varchar) + '/' + cast(DatePart(yy,@start) as varchar),
                       
103
               
)
       
)
   
end

   
if @type = 'y'
   
begin
       
set @to = dateadd(year, 1,
               
convert
               
(      
                        datetime
,
                       
'1/1/' + cast(DatePart(yy,@start) as varchar),
                       
103
               
)
       
)
   
end

   
while @to < @finish
   
begin
       
insert @ranges values (@from, @to)
       
set @from = @to
       
if @type = 'd'
               
set @to = dateadd(day, 1, @to)
       
if @type = 'm'
               
set @to = dateadd(month, 1, @to)
       
if @type = 'y'
               
set @to = dateadd(year, 1, @to)
   
end

   
insert @ranges values (@from, @finish)

   
return
end