how to group by Date, when Date column has yyyy-mm-dd hh:mm:ss FORMAT

[seagull]

in my MSSQL tables the date is in the format of
yyyy-mm-dd hh:mm:ss.

I am trying to get a report in the format:
Date Total Minutes
2006-Mar-1 1100
2006-Mar-2 5004
2006-Mar-3 8000

and so on.

The group by DATE dont give the required output as the TIME also is in the DATE column.
Any ideas are appreciated

[obsidian]

[!--quoteo(post=352794:date=Mar 8 2006, 06:08 AM:name=seagull)--][div class=\'quotetop\']QUOTE(seagull @ Mar 8 2006, 06:08 AM) [snapback]352794[/snapback][/div][div class=\'quotemain\'][!--quotec--]
in my MSSQL tables the date is in the format of
yyyy-mm-dd hh:mm:ss.

I am trying to get a report in the format:
Date Total Minutes
2006-Mar-1 1100
2006-Mar-2 5004
2006-Mar-3 8000

and so on.

The group by DATE dont give the required output as the TIME also is in the DATE column.
Any ideas are appreciated
[/quote]

i'm not sure if this will work in MSSQL or not, but in MySQL, you can call the DATE() function on the column that has a datetime, and it only returns the date portion of it, so you can group by it... something like this:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DATE(dateColumn) [color=green]AS[/color] date, [color=blue]SUM[/color](minutes) [color=green]as[/color] total GROUP BY date;
[!--sql2--][/div][!--sql3--]

hope this helps

[GeoffOs]

You could use the convert function on your datetime column to change it into a date only column, for example
[code]select convert(varchar(11), getdate(), 106)[/code]
which returns
[code]17 May 2006[/code]

More details are available from msdn here [a href=\"http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp\" target=\"_blank\"]Cast and Convert MSDN Article[/a]

I think you can use this result within your group by clause, though I have not tested this.