Problem with Sql Server query using BETWEEN in where with DATETIME

I have a Stored Procedure that loops through the months in the fiscal year and does a count for the items in each month. I know for a fact there are 176 items, but when I run this it returns a total count of 182. I tried removing one second from @EndDate, but then my total count was 165. So I'm either counting items twice, or not counting all of them. Can anyone help with what I'm doing wrong here? Below is a stripped down version of what I'm doing:

DECLARE @Date DATETIME
DECLARE @EndDate DATETIME

SELECT @Date = CAST((@Year - 1) as VARCHAR) + '-07-01'
SELECT @EndDate = DATEADD(Month, 1, @Date)


DECLARE @Count INT
SELECT @Count = 0
WHILE @Count < 12

BEGIN

    SELECT 
        COUNT(yai.ID)
    FROM
        table_1    yai 
        INNER JOIN table_2 yat ON yai.ID = yat.ID 
    WHERE
        (yat.Date_Received BETWEEN CONVERT(VARCHAR, @Date, 101) AND CONVERT(VARCHAR, @EndDate, 101))  AND 
        yai.Pro_Type = @Value AND yat.Type = 'PC'

    SELECT @Count = @Count + 1
    SELECT @Date = DATEADD(MONTH, 1, @Date)
    SELECT @EndDate = DATEADD(MONTH, 1, @EndDate)

END

Upvotes: 0

Answers (7)

Roshna Omer

Reputation: 721

I found this question because I had troubles with using string date in queries using Between and I was using metadata, my co-worker helped me, so I hope this helps you too:

N'convert(date,[fld_myDate]) Between  convert(date,''01/01/2016'') AND convert(date,''08/26/2017'') '

Upvotes: 0

Mike C.

Reputation: 4977

Why not convert the calendar months into absolute integer references like this:

DECLARE @BeginDate datetime = '7/1/2008'
DECLARE @EndDate datetime = '6/30/2009'
--
-- Convert calendar months into an absoulte integer:
--
DECLARE @BeginMonth int = (DatePart(year, @BeginDate) * 12) + DatePart(month, @BeginDate)
DECLARE @EndMonth int = (DatePart(year, @EndDate) * 12) + DatePart(month, @EndDate)

SELECT COUNT(yai.ID)
FROM table_1 yai
INNER JOIN table_2 yat ON yai.ID = yat.ID
WHERE (DatePart(year, yat.Date_Received) * 12) + DatePart(month, yat.Date_Received) 
    BETWEEN @BeginMonth AND @EndMonth)
AND yai.Pro_Type = @Value
AND yat.Type = 'PC'

Upvotes: 0

Jonas Elfström

Reputation: 31428

From the top of my head.

SELECT DATEPART(month, yat.Date) as month, COUNT(yai.ID)
FROM table_1 yai 
INNER JOIN table_2 yat ON yai.ID = yat.ID 
WHERE
    yai.Pro_Type = @Value AND yat.Type = 'PC' 
    AND DATEPART(year, yat.Date)=@Year
GROUP BY DATEPART(month, yat.Date)
ORDER BY DATEPART(month, yat.Date)

Upvotes: 0

KM.

Reputation: 103579

this is a classic case of an unnecessary loop is SQL code, but if you want to solve this with a loop, change your select in the loop from:

 SELECT 
        COUNT(yai.ID)

to:

 SELECT 
     @Date,@EndDate,*

and then look at the output and check the rows returned

Upvotes: 0

D'Arcy Rittich

Reputation: 171351

Couldn't you do this instead? The following query will give you counts grouped by month. If you put this in a view, you can then select from the view and use a where clause to filter by the year and month you are interested in.

select year(yat.Date_Received) as year, month(yat.Date_Received) as month, count(*) as count
    count(yai.ID)
from table_1 yai 
inner join table_2 yat on yai.ID = yat.ID 
where yai.Pro_Type = 'some_value' 
    and yat.Type = 'PC'
group by year(yat.Date_Received), month(yat.Date_Received)

Upvotes: 0