Ben Fidge wrote:

> How would I group records by date from a table where each record has a
> smalldatetime field called CreationDateTime that is auto-populated using
> GetDate().
>
> I need to produce a trends graph and want display the number of records
> created per day. Obviously, my CreationDateTime field stored both both date
> and time.
>
> Thanks
>
> Ben

"Ben Fidge" <ben.fidge@nospambtopenworld.com> wrote in message
news:%23Yd2cbMBGHA.740@TK2MSFTNGP12.phx.gbl...
> How would I group records by date from a table where each record has a
> smalldatetime field called CreationDateTime that is auto-populated using
> GetDate().
>
> I need to produce a trends graph and want display the number of records
> created per day. Obviously, my CreationDateTime field stored both both
> date and time.
>
> Thanks
>
> Ben
>
>

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:u0D$9fMBGHA.1032@TK2MSFTNGP11.phx.gbl...
> This isn't going to be very snappy, but...
>
>
> SELECT
> dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreationDateTime)),
> COUNT(*)
> FROM
> yourTable
> WHERE
> CreationDateTime >= ?
> AND CreationDateTime < ?
> GROUP BY
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreationDateTime))
> ORDER BY
> 1
>
>
> Note that if there are days in your date range with no data, they will not
> show up in the result set. If you want to have a row for every day, even
> when there are no relevant rows, use a calendar table (see
> http://www.aspfaq.com/2519 for some examples).
>
> You may want to consider adding a computed or static column that holds the
> date only, if you are going to use a lot of queries like this. If you do
> that, you will want to experiment with your clustered index, and whether
> it resides on the column with both date and time, or on the column with
> just the date. Your best scenario depends on whether you are querying by
> range or just analyzing the entire table, and what else this table is
> being used for...
>
> A
>
>
> "Ben Fidge" <ben.fidge@nospambtopenworld.com> wrote in message
> news:%23Yd2cbMBGHA.740@TK2MSFTNGP12.phx.gbl...
>> How would I group records by date from a table where each record has a
>> smalldatetime field called CreationDateTime that is auto-populated using
>> GetDate().
>>
>> I need to produce a trends graph and want display the number of records
>> created per day. Obviously, my CreationDateTime field stored both both
>> date and time.
>>
>> Thanks
>>
>> Ben
>>
>>
>
>