How To Group By Days In SQL Server

Today I have had to optimize an statement in a database SQL Server 2008 R2, the reason for this post is not the performance itself of the sentence, if not rather the way to the group by for days, exactly it used the following clause;

GROUP BY DATEDIFF(DAY, 0, DateOfOperation)

Objectively and from a practical point of view, it works and it is correct, at least to some extent. One of the things I’ve always said that as a rule all SQL statements inside a database, including the framework (Transact SQL in Microsoft, PL/SQL in Oracle) is alive and evolves over time and with the the provider’s own technology. In fact in this case it is better to use or become clearer with the following function;

GROUP BY CAST(DateOfOperation AS Date)

Besides, to see the difference that makes one and another I put these querys;

 

 
select DATEDIFF(DAY, 0, GETDATE ())    

// Return:  42450

select CAST(getdate()  AS Date)         

// Return: 2016-03-23

HTH – Antonio NAVARRO

 

Advertisements

One response to “How To Group By Days In SQL Server

  1. Pingback: How To Group By Days In SQL Server | SutoCom Solutions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s