Post reply

return only the date from datatime

  • September 26, 2008 at 12:24 am

    #193591

    Hi

    Is there a function for returning only the date from a datetime field?

    I have to make some counts and I have to group them after and day and after hour...but now I'm trying to solve the day problem ....

    But if I use : group by day(date) the data is lost

    If I use group by date it takes also the hour and my count has no value

    Any ideea....

    Thanks

  • September 26, 2008 at 12:31 am

    #877437

    select convert(char(10),getdate(),101)

  • September 26, 2008 at 12:48 am

    #877444

    SELECT dateadd(dd, datediff(dd,0, getdate()),0)

    The varchar method works, but is slower. See - http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 26, 2008 at 12:48 am

    #877445

    Thank you

    Is working.I've tryied CTE with split for the date and it wasn't working properly...

    Nice solution:)

  • September 26, 2008 at 1:00 am

    #877452

    If you are trying to get a count by Date, you can use

    SELECT CONVERT(CHAR(10), Your_Date, 121) as GroupingDate, COUNT(*)

    FROM Your_Table

    GROUP BY CONVERT(CHAR(10), Your_Date, 121)

    ORDER BY GroupingDate

    If you are trying to get a count by Date and Hour, you can use

    SELECT CONVERT(CHAR(13), Your_Date, 121) as GroupingDateHour, COUNT(*)

    FROM Your_Table

    GROUP BY CONVERT(CHAR(13), Your_Date, 121)

    ORDER BY GroupingDateHour

  • September 26, 2008 at 5:51 am

    #877571

    select datepart(dd,getdate()) will give the only the date as 26

    select Convert(varchar(10),getdate(),101) will returns the date as '09/26/2008'

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply