ARJUN ARJUN - 1 month ago 7
SQL Question

Datetime behaviour in T-SQL

select * from DB..SMS_new where created_on>'02-Nov-2016'


I use this query with exception of date 3 nov but i am getting

Created_On
2016-11-02 05:59:50.617
2016-11-02 08:22:06.137
2016-11-02 11:01:53.130
2016-11-02 12:25:45.460
2016-11-02 13:53:38.050
2016-11-03 05:25:50.247
2016-11-03 08:38:41.753
2016-11-02 12:07:48.337
2016-11-02 12:06:51.447
2016-11-02 12:07:01.013


and my date column is of
datetime
datatype

Answer

Because there is data for some other month that has a 28th. I would recommend:

select cast([datetime] as date), count(*)
from (select distinct cm.*
      from logs_currentmonth cm
     ) cm
where [datetime] >= '2016-10-01'
group by cast([datetime] as date)
order by cast([datetime] as date);

If you don't need the select distinct, then don't use it! It just slows down the query.