Jump to content


Photo

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


  • Please log in to reply
2 replies to this topic

#1 seagull

seagull
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 08 March 2006 - 11:08 AM

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

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 08 March 2006 - 12:42 PM

[!--quoteo(post=352794:date=Mar 8 2006, 06:08 AM:name=seagull)--][div class=\'quotetop\']QUOTE(seagull @ Mar 8 2006, 06:08 AM) View Post[/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) AS date, SUM(minutes) as total GROUP BY date;
[!--sql2--][/div][!--sql3--]

hope this helps
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 GeoffOs

GeoffOs
  • Members
  • PipPip
  • Member
  • 24 posts
  • LocationCheshire, England

Posted 17 May 2006 - 03:54 PM

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

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.
Beyond a critical point within a finite space, freedom diminishes as numbers increase....[br]The human question is not how many can possibly survive within the system, but what kind of existence is possible for those who do survive."[br]-- Frank Herbert - Dune




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users