Select min (date), max (date) and group by day from one column - SQL

Question

I'm going crazy soon, please help.

I have one column containing datetime values.

I need to find min and max for each day.

The data is as follows:

 2012-11-23 05:49:26.000 2012-11-23 07:55:43.000 2012-11-23 13:59:56.000 2012-11-26 07:51:13.000 2012-11-26 10:23:31.000 2012-11-26 10:25:09.000 2012-11-26 16:22:22.000 2012-11-27 07:30:03.000 2012-11-27 08:53:47.000 2012-11-27 10:40:55.000 

This is what has been done so far.

 select distinct(convert(nvarchar, datum, 112)), min(datum), max(datum) from myTable 

but when I

 Group by 

I group all 3 columns ...

It seems I am not working to set my first choice as ColName and Group on this

This is what I want

 20121123 | 2012-11-23 05:49:26.000 | 2012-11-23 13:59:56.000 20121126 | 2012-11-26 07:51:13.000 | 2012-11-26 16:22:22.000 20121127 | 2012-11-27 07:30:03.000 | 2012-11-27 10:40:55.000 

Answer 1

Convert the column to GROUP BY .

 select min(datum), max(datum), CONVERT(varchar(8), datum, 112) from dateTable group by CONVERT(varchar(8), datum, 112) 

Here is the fiddle

Answer 2

In SQL Server 2008+, you can use the date data type instead of converting to a character string:

 select cast(datum as date), min(datum), max(datum) from myTable group by cast(datum as date);