grouping by a datetime column

i want to group the records in a table by day , using a datetime column.
Therefore I have to get rid of the time of that column before grouping.
What is the proper way to do that?
thnks..prefect wrote:
> i want to group the records in a table by day , using a datetime column
.
> Therefore I have to get rid of the time of that column before grouping.
> What is the proper way to do that?
> thnks..
>
GROUP BY
DATEPART(month, datevalue),
DATEPART(day, datevalue),
DATEPART(year, datevalue)|||SELECT
DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
COUNT(*)
FROM [dbo].[TableName]
GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
ORDER BY 1;
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:%23n$A18HkGHA.4304@.TK2MSFTNGP03.phx.gbl...
>i want to group the records in a table by day , using a datetime column.
>Therefore I have to get rid of the time of that column before grouping.
> What is the proper way to do that?
> thnks..
>|||> GROUP BY
> DATEPART(month, datevalue),
> DATEPART(day, datevalue),
> DATEPART(year, datevalue)
FYI, on a large table, this can be a significant performance hit...
In fact, my solution is only marginally better. The best solution would
probably combine a static calendar table (see http://www.aspfaq.com/2519 for
some practical usage).|||that is what i look for.
thanks..
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eF23h$HkGHA.1264@.TK2MSFTNGP05.phx.gbl...
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1;
>
> "prefect" <uykusuz@.uykusuz.com> wrote in message
> news:%23n$A18HkGHA.4304@.TK2MSFTNGP03.phx.gbl...
>|||Aaron Bertrand [SQL Server MVP] wrote:
> FYI, on a large table, this can be a significant performance hit...
> In fact, my solution is only marginally better. The best solution would
> probably combine a static calendar table (see http://www.aspfaq.com/2519 f
or
> some practical usage).
>
Agreed.|||Aaron , I want to send the DateColumnName to a UDF for some processing,
then return something.
But I have a error like "DateColumnName is not in group by clause..."
My usage is as follows:
SELECT
DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
dbo.MyUdf( DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))),
COUNT(*)
FROM [dbo].[TableName]
GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
ORDER BY 1
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eF23h$HkGHA.1264@.TK2MSFTNGP05.phx.gbl...
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1;
>
> "prefect" <uykusuz@.uykusuz.com> wrote in message
> news:%23n$A18HkGHA.4304@.TK2MSFTNGP03.phx.gbl...
>|||What exactly are you doing, formatting it for the client? Why don't you let
the presentation/client tier do this? What does dbo.MyUDF do, exactly, that
CONVERT() with a style option couldn't do?
Anyway, I don't see dbo.MyUDF() in the group by clause. Columns that exist
in the SELECT list that are not constants or aggregates must also appear in
GROUP BY clause. But a slightly more efficient way would be to perform the
function against the result instead of during the aggregation:
SELECT
dt,
dbo.MyUDF(dt),
cnt
FROM
(SELECT
dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
cnt = COUNT(*)
FROM [dbo].[TableName]
GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
) x
ORDER BY 1;
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:OPU84WIkGHA.4660@.TK2MSFTNGP05.phx.gbl...
> Aaron , I want to send the DateColumnName to a UDF for some processing,
> then return something.
> But I have a error like "DateColumnName is not in group by clause..."
> My usage is as follows:
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> dbo.MyUdf( DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1|||I created a computed Column for DateColumnName
as DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
and used this computed column for grouping and parameter for MyUdf. it is
working.
But i would wanna know if there is a better way..
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:OPU84WIkGHA.4660@.TK2MSFTNGP05.phx.gbl...
> Aaron , I want to send the DateColumnName to a UDF for some processing,
> then return something.
> But I have a error like "DateColumnName is not in group by clause..."
> My usage is as follows:
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> dbo.MyUdf( DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1
>
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eF23h$HkGHA.1264@.TK2MSFTNGP05.phx.gbl...
>|||
> What exactly are you doing, formatting it for the client? Why don't you
> let the presentation/client tier do this?
yes it should be this way. But for some reason off my hand , i am not able
to do
in the presentation layer.

> What does dbo.MyUDF do, exactly, that CONVERT() with a style option
> couldn't do?
no, unfortunately..

> Anyway, I don't see dbo.MyUDF() in the group by clause. Columns that
> exist in the SELECT list that are not constants or aggregates must also
> appear in GROUP BY clause. But a slightly more efficient way would be to
> perform the function against the result instead of during the aggregation:
>
> SELECT
> dt,
> dbo.MyUDF(dt),
> cnt
> FROM
> (SELECT
> dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> cnt = COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ) x
> ORDER BY 1;
i will try that, can you comment my other post?

> "prefect" <uykusuz@.uykusuz.com> wrote in message
> news:OPU84WIkGHA.4660@.TK2MSFTNGP05.phx.gbl...
>