10:24 AM
Sometimes it is interesting to know number of edits/posts/votes/something per day/month. For example, when some changes in various trends are discussed on meta.
2 hours later…
12:31 PM
@Martin to group by day can be achieved by casting (or converting) a datetime to a date type, so
group by cast(creationdate as date)
. The date type doesn't allow for a time component so it will simply drop that from the datetime values. For month there isn't such trick, but you don't need to convert to str and concat. A group by yesr(creationdate), month(creationdate)
will work.
You might like the
format
function: docs.microsoft.com/en-us/sql/t-sql/functions/… it allows you to format the values from several types into a varchar using the .Net composite string formatting options. Select format(getdate(), 'yyyy-MM' )
gives you 2018-08 in one go.
3 hours later…
3:59 PM
I found an example of such query here: Comments per Day. (Number of comments per day for a specific user.)
« first day (21 days earlier) ← previous day next day → last day (2087 days later) »