Sql server get only the date from a DateTime and filter on it

Question

I have a database in Sql Server with TimeSpan column the date in the TimeSpan column format is : 14/03/2022 18:50:21

I was looking for a way how to sort the data by day and month. The only problem is i have also time included into the database which seems to be an issue to filter on. Somehow i'm not able to only filter by days/months. Its not giving me an error which i understand i just need a wat to provide a time...

I've tried :

With wild cards.

Select * from MBR where TAG='FT_AV_03' AND TimeStamp LIKE '03/14/2022 __:__:__' 

Select * from MBR where TAG='FT_AV_03' AND TimeStamp LIKE '03/15/2022 %'

With Contains

select * from MBR where TimeStamp like '03/15/2022%'

Answer 1

No, don't ever do TimeStamp LIKE <anything> because it's not a string. You want a range query so that SQL Server doesn't have to do anything to the column first (like convert it to a different type or translate it into a specific format), and potentially use an index (now or if one exists in the future):

WHERE [TimeStamp] >= '20220314'
  AND [TimeStamp] <  '20220315';

As for sorting, well, it's not quite clear what you want. It could be simply:

ORDER BY [TimeStamp];

But if you want to order rows within a specific day by something else, if you've already filtered to just that day, then just ORDER BY <that>. If you want something else, you'll need to be more specific.

• Dating Responsibly