GROUP BY and dates in SQL (again)

trying to using GROUP BY statement on a SQL Server DB.
GROUP BY [date] groups by date and time.
Tried using the CONVERT function but no success...

any tips?

tried DATEPART()?

this is what I've already tried:

CONVERT(DATETIME, [date], 5) ' 5: dd-mm-yy
result: still returns time

CONVERT(DATETIME, [date], 6) ' 6: dd mmm yy
result: still returns time

CONVERT(CHAR(12), [date], 5) ' 5: dd-mm-yy
result: returns 'dd-mm-yy' but doesn't sort as a date, sorts alphabetically instead
i.e.: date between 28-nov and 02-dec shows up in this order:
01 dec 04
02 dec 04
28 nov 04
29 nov 04
30 nov 04


I'm about to try datepart... I'll let you know

great, I think I'm nearly there....

this worked:
DatePart(dt, [date]) ' day of year

but now I have to somehow display the actual dates because this makes much more sense:
30 orders on nov-27


than this:
30 orders on day 317

GOT IT!

I used DATEPART to group by year and day and used CONVERT to return the date I want to display as a string. then I sort by year and day.........

I'll post it in case somebody else comes across the same problem:

Code: Select all

SELECT DATEPART(yy, Orders.[date]) AS [y],
       DATEPART(dy, Orders.[date]) AS [d], 
       CONVERT(CHAR(12), Orders.[date], 106) AS [date],
       Count(Orders.[order]) AS n
FROM
       [....]
WHERE
       [....]
GROUP BY
       DATEPART(yy, Orders.[date]),
       DATEPART(dy, Orders.[date]),
       CONVERT(CHAR(12), Orders.[date], 106)
ORDER BY
       DATEPART(yy, Orders.[date]),
       DATEPART(dy, Orders.[date])

this site was very helpfull:
http://manuals.sybase.com/onlinebook...y=convert+date