where clause is not working with group by

Question

Hello All,

My query is:

Select Reg_year, count(*) as Total
From t_Data FR
Where fr.STATE_CD=35
and fr.Insert_DATA_Date >= '2017-01-01 15:25:16.437'
and fr.Insert_DATA_Date <= '2019-12-01 15:25:16.437'
group by Reg_year

Problem: date filter is not working.

Output comes like:

Reg_Year | Total

-------------------------------

2015 | 6

2016 | 9

2017 | 13

2018 | 11

Thanks in advance

Jignesh Kumar · Answer

Hi Ankit, You need to convert your date field into datetime.

Rajanikant Hawaldar · Answer

https://stackoverflow.com/questions/13869640/filter-by-dates-in-sql

Ankit Shukla · Answer

Thanks for contributing all of you. Please find attached file. I don't understand , why 'where' clause is not working and it is not filtering years. Thanks

Amit Mohanty · Answer

Select Reg_year, count (*) as Total From t_Data FR Where fr.Insert_DATA_Date between CONVERT (datetime, '2017-10-17' ) AND CONVERT (datetime, '2020-10-18' ) and fr.STATE_CD=35 group by Reg_year Hey Ankit there is nothing wrong in the above query. After that if you get wrong data then please check your table data. May be there is wrong data in your table.

Rajanikant Hawaldar · Answer

what is the expected output? is count coming wrong? may issue with time part

Ankit Shukla · Answer

Hello Sir, I have tried with between claues. But problem remain same. query: Select Reg_year, count(*) as Total From t_Data FR Where fr.Insert_DATA_Date between CONVERT(datetime,'2017-10-17') AND CONVERT(datetime,'2020-10-18') and fr.STATE_CD=35 group by Reg_year

Rajanikant Hawaldar · Answer

because your not using 'between' https://stackoverflow.com/questions/19451767/datetime-between-statement-not-working-in-sql-server