Working with Datetime values in MS SQL Server 2008, I need to group by data only?

Question

I have been presented with a project that has a field that I need report on. The field is a Datetime field and I need to know how many activities of a specific type took place during a specific time period and display it by date...NOT Datetime. Meaning, that they want:

Activities:
X___Y___Date:
123...45....10/01/2011
96.....24....10/02/2011
112...13....10/03/2011

What I keep getting is:

Activities:
X___Y___Date:
0......1.......10/01/2011 07:53:25.000
1......1.......10/01/2011 08:54:16.000
0......1.......10/01/2011 08:55:12.000
0......1.......10/01/2011 09:27:19.000
1......1.......10/01/2011 09:37:55.000
1......0.......10/01/2011 10:59:41.000
And so on.

I am NOT a SQL developer but I was given the project anyway and I would like some input. The last time I worked in MS SQL was 10 years ago. Thanks.

Answer 1

You could also use



SELECT X, Y, [Date] = CAST(Date AS DATE) FROM...

Answer 2

Convert the field into a type 101 date.



SELECT X, Y, Convert(DateTime, Date, 101) AS Date FROM Activities;

Answer 3

sq. Server has 6 diverse date and time datatypes, smalldatetime, datetime, datetime2, datetimeoffset, date, and time. all and sundry has diverse accuracy from a hundred nanoseconds to a million day.