GROUP BY DateTime

Dear All,
Did a research, seems like the only solution in SQL Server is:
SELECT CONVERT(CHAR(8), RecTime , 112) FROM RecTbl GROUP BY
CONVERT(CHAR(8), RecTime , 112);
The RecTime is a DateTime field, and I wish to sort by the Date
Component.
Any simpler solution?
<ckkwan@.my-deja.com> wrote in message
news:58c5c9b0-1fd1-43f5-aac3-360c83d7b2a7@.w1g2000prd.googlegroups.com...
> Dear All,
> Did a research, seems like the only solution in SQL Server is:
> SELECT CONVERT(CHAR(8), RecTime , 112) FROM RecTbl GROUP BY
> CONVERT(CHAR(8), RecTime , 112);
> The RecTime is a DateTime field, and I wish to sort by the Date
> Component.
> Any simpler solution?
In SQL2000/2005, not really. But really, it's not that complex.
There's variations on this, but they're all about the same idea.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||ckkwan@.my-deja.com wrote:
> Dear All,
> Did a research, seems like the only solution in SQL Server is:
> SELECT CONVERT(CHAR(8), RecTime , 112) FROM RecTbl GROUP BY
> CONVERT(CHAR(8), RecTime , 112);
> The RecTime is a DateTime field, and I wish to sort by the Date
> Component.
> Any simpler solution?
Definitely not the only solution!
Are there any simpler solutions? No. But there is a faster solution.
AFAIK this is the best performing solution:
SELECT DATEADD(day, DATEDIFF(day,0,us_Hitdate) ,0)
FROM RecTbl
GROUP BY DATEDIFF(day, 0, us_Hitdate)
Gert-Jan
SQL Server MVP
|||Gert
You meant
GROUP BY DATEADD(day, DATEDIFF(day,0,EstimateDate) ,0)
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:480657AB.F6B90AE9@.toomuchspamalready.nl...
> ckkwan@.my-deja.com wrote:
> Definitely not the only solution!
> Are there any simpler solutions? No. But there is a faster solution.
> AFAIK this is the best performing solution:
> SELECT DATEADD(day, DATEDIFF(day,0,us_Hitdate) ,0)
> FROM RecTbl
> GROUP BY DATEDIFF(day, 0, us_Hitdate)
> --
> Gert-Jan
> SQL Server MVP
|||Gert is correct, no need to have the DATEADD in the GROUP BY as it does not
change the grouping. Try this:
SELECT DATEADD(day, DATEDIFF(day,0,us_Hitdate) ,0), COUNT(*)
FROM (SELECT CURRENT_TIMESTAMP
UNION ALL
SELECT DATEADD(hh, 1, CURRENT_TIMESTAMP)) AS T(us_HitDate)
GROUP BY DATEDIFF(day, 0, us_Hitdate);
Plamen Ratchev
http://www.SQLStudio.com
|||Nope :-)
Gert-Jan
Uri Dimant wrote:[vbcol=seagreen]
> Gert
> You meant
> GROUP BY DATEADD(day, DATEDIFF(day,0,EstimateDate) ,0)
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:480657AB.F6B90AE9@.toomuchspamalready.nl...