where clause is not working with group by

Ankit Shukla

6y919SQL

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

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar6y ago
Hi Ankit,

You need to convert your date field into datetime.
0
Rajanikant Hawaldar6y ago
https://stackoverflow.com/questions/13869640/filter-by-dates-in-sql
0
Ankit Shukla6y ago
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
0
Amit Mohanty6y ago
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.
0
Rajanikant Hawaldar6y ago
what is the expected output? is count coming wrong? may issue with time part
0
Ankit Shukla6y ago
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

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