Thursday, 3 June 2010

Group By Date Time without milliseconds in T-SQL

There are lot of requirements which needs some complex logic and put maximum effort to implement them. The below scenario is the one I want to explain in writing T-SQL queries.

The requirement is very simple, I want to apply group by on DateTime field in database which does not includes the milliseconds part from the time. So, on the UI I need to group by and show records with seconds and but not with milliseconds.

In my first try, I just wrote the T-SQL query by directly applying the GROUP BY clause on the DateTime field and found that milliseconds are also including. Which is not matching with what exactly I was looking for. So, tested lot of scenarios and nothing works. I am out of ideas on how to trim the milliseconds and apply group by.

After spent some good time on it, got an idea and not sure this is the best and efficient way of implementing.

SELECT CAST(CAST(CONVERT(Date,Datesent) AS Varchar(20)) + ' ' + CAST(CONVERT(TIME(0),Datesent) AS Varchar(20)) AS Datetime) AS DateSent,
Name, Department,
    FROM [EmployeHistory]
    WHERE EmployeID = @EmployeID
    GROUP BY CAST(CAST(CONVERT(Date,Datesent) AS Varchar(20)) + ' ' + CAST(CONVERT(TIME(0),Datesent) AS Varchar(20)) AS Datetime),
Name, Department
ORDER BY DateSent Desc

So, in the above query, if you observe take the GROUP BY clause out and analyze it.
GROUP BY CAST(CAST(CONVERT(Date,Datesent) AS Varchar(20)) + ' ' + CAST(CONVERT(TIME(0),Datesent) AS Varchar(20)) AS Datetime)

I am doing some way to get  the things work out. :) So, don't be surprise. I am doing nothing here, other than taking the Date part from the date time and taking the Time part from the date time without milliseconds and finally casting it back to date by clubbing those two back...

Do you think, this is the best and efficient way? I am sure this will work without any issues. But, the main thing is even though it is looking like simple it took me time to get the solution for it. Hope this helps and please let me know, if you know any better solution than this.

No comments:

Post a Comment