Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2007
    Posts
    197

    Smile Unanswered: group by datetime

    here is problem with datetime data type


    when taking

    group by createdate

    that is in 8/8/2007 12:00:00 AM format
    I dont want group for time 12:00:00 AM only group by for
    date 8/8/2007 which format is mm/dd/yyyy

    how can I do

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT DATEADD(dd,DATEDIFF(dd,0,createdate),0) as thedate
    , COUNT(*) as rows
    FROM ...
    GROUP BY DATEADD(dd,DATEDIFF(dd,0,createdate),0)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    Or, if u want to get it as mm/dd/yyy

    select count(*), convert(varchar(10),createdate,101) from Table1 group by convert(varchar(10),createdate,101)

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    good news on this front with the latest CTP for sql server 2008. WE ARE FINALLY GOING TO HAVE SEPERATE DATA TYPES FOR THE DATE AND THE TIME. it's about friggin' time. this was always one the most annoying product features. i heard they tried to slip it into 2K5 but they ran out of time.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus
    ... but they ran out of time.
    badump-bump tsshhhhh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Thrasymachus
    good news on this front with the latest CTP for sql server 2008. WE ARE FINALLY GOING TO HAVE SEPERATE DATA TYPES FOR THE DATE AND THE TIME. it's about friggin' time. this was always one the most annoying product features. i heard they tried to slip it into 2K5 but they ran out of time.
    Ya know, this is an issue that has never really caused any problem for me, though I certainly hear enough other people whining about it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2007
    Posts
    197

    Smile

    Thanks to you all I got my answer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •