Jump to content

Archived

This topic is now archived and is closed to further replies.

seagull

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
[!--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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.