Select Query ... group by Date

Most of the time we use, GROUP BY, in our sql queries, sometimes it may require to write a query Group By Date.

In this case, if the date field is DateTime in your table column, it won’t give correct data because it contains Date + Time, the time part makes difference, so we have to ignore the time part when writing query for group by with Date column.

What what what…????

Relax and see the example below you will understand clearly…

I have a table:

CREATE TABLE [dbo].[Table_1](

[name] [nchar](10) NULL,

[date] [datetime] NULL,

[add] [nchar](10) NULL

)

Note: date may be 2009-02-02 00:00:00.000 or 2009-02-02 08:20:10.000

Now compare both results below:

=======================================================

select date,[name],[add] from Table_1 group by date,[name],[add]

date name add

----------------------- ---------- ----------

2009-02-02 00:00:00.000 a 111

2009-02-02 00:00:40.000 ccc 333

2009-02-02 00:01:00.000 bb 222

2009-02-02 00:07:00.000 aa 66

2009-02-02 01:00:00.000 aaa 77

2009-02-03 00:00:40.000 ddd 444

2009-02-03 00:00:40.000 eee 555

(7 row(s) affected)

=======================================================

select

DATEADD(dd, 0, DATEDIFF(dd, 0, date)) date,

[name],

[add]

from

Table_1

group by

DATEADD(dd, 0, DATEDIFF(dd, 0, date)),

[name],

[add]

date name add

----------------------- ---------- ----------

2009-02-02 00:00:00.000 a 111

2009-02-02 00:00:00.000 aa 66

2009-02-02 00:00:00.000 aaa 77

2009-02-02 00:00:00.000 bb 222

2009-02-02 00:00:00.000 ccc 333

2009-02-03 00:00:00.000 ddd 444

2009-02-03 00:00:00.000 eee 555

(7 row(s) affected)

=======================================================

Look at First column of both restult,in second result all time are set to 00:00:00:000

Observ the first and second column, First result, even though dates are same, they appears to be different, and solution/fix is in second result…

Hope it helps you some where… some time…

Happy coding !!! J