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%'
CodePudding user response：
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.