date query problem

why aint this return the records for current day if al FROM and TO variabels is set to current date?

SELECT TOP 20 VLF_VirusName, COUNT(VLF_VirusName) AS number
FROM tb_AVVirusLog
WHERE (CLF_ComputerName LIKE N'%') AND (VLF_InfectionDestination LIKE N'%') AND (VLF_InfectionSource LIKE N'%') AND (CONVERT(varchar,
CLF_LogGenerationTime, 120) BETWEEN @.from_year + @.from_month + @.from_day AND @.to_year + @.to_month + @.to_day)
GROUP BY VLF_VirusName
ORDER BY COUNT(VLF_VirusName) DESC

This works (it gives me current days records), but i want to be able to specify end date instead of current date.

SELECT TOP 20 VLF_VirusName, COUNT(VLF_VirusName) AS number
FROM tb_AVVirusLog
WHERE (CLF_ComputerName LIKE N'%') AND (VLF_InfectionDestination LIKE N'%') AND (VLF_InfectionSource LIKE N'%') AND (CONVERT(varchar,
CLF_LogGenerationTime, 120) BETWEEN @.from_year + @.from_month + @.from_day AND CONVERT(varchar, GETDATE(), 120))
GROUP BY VLF_VirusName
ORDER BY COUNT(VLF_VirusName) DESCYou should "Cast" the @.from_year, @.from_Month, @.from_day, etc variables as char or carchar, if not the "@.from_year + @.from_month + @.from_day" contruct will simply add the _integers_ contained in those variables.

So, I would replace the "@.from_year + @.from_month + @.from_day"

with

"cast(@.from_year as char(4)) + '-' + cast(@.from_month as char(2)) + '-' + cast(@.from_day as char(2)"

you should do similar for the @.to_ variables...|||why aint this return the records for current day if al FROM and TO variabels is set to current date?

Oh my.

Post the DDL of your table, and why are your input date values broken up by year/month/day?|||thx for help.
its not broken up in table its only broken up in query so that users can put in these values in a form. maybe not the correct way to do it but it works for me :)

and what u mean by DDL?|||Perhaps this forum would be more helpful:

http://rinkworks.com/dialect/dialectp.cgi?dialect=redneck&url=http%3A%2F%2Fwww.dbforums.com|||ye that was helpful and creative...