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:
>> 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 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:
> 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:
> >>
> >> 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|||Plamen
Have you run your script on SS2000?
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:9814BC3D-0B4E-401C-B18D-868581495690@.microsoft.com...
> 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|||Yes, that was a limitation in SQL 2000... But I would assume by today's
standards SQL 2005 is the base to measure. :)
Plamen Ratchev
http://www.SQLStudio.com|||I'd prefer ask people what version they are using. There are still lots of
businesses use SQL Server 2000/
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:6D6390C6-57B3-470F-80FC-8E4DA9F4A3C5@.microsoft.com...
> Yes, that was a limitation in SQL 2000... But I would assume by today's
> standards SQL 2005 is the base to measure. :)
> Plamen Ratchev
> http://www.SQLStudio.com|||Yes, SQL Server 2000 or earlier will not accept the solution I posted.
In that case, it is easier to keep the Selection List items in sync with
the GROUP BY items.
However, if you really wanted to, you could achieve the same thing on
these version by writing:
SELECT DATEADD(day,MAX( DATEDIFF(day,0,my_date_column) ),0)
FROM ...
GROUP BY DATEDIFF(day, 0, my_date_column)
--
Gert-Jan
Uri Dimant wrote:
> I'd prefer ask people what version they are using. There are still lots of
> businesses use SQL Server 2000/
> "Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
> news:6D6390C6-57B3-470F-80FC-8E4DA9F4A3C5@.microsoft.com...
> > Yes, that was a limitation in SQL 2000... But I would assume by today's
> > standards SQL 2005 is the base to measure. :)
> >
> > Plamen Ratchev
> > http://www.SQLStudio.com