Showing results grouped by month using a date field

Other SQL Server Topics
1

Hello

I would like to group results by months, surely this is very easy so sorry for that but I am an absolute beginner!

My simple query is:

select number, date_created
from student
where date_created >= '2016-01-01'
and date_created <= '2016-09-30'

then I would use group by and maybe datediff or datepart?

Thanks in advance

2

Something like below; not sure if you wanted a sum() or what for the number:

select sum(number) as number_total, 
    dateadd(month, datediff(month, 0, date_created), 0) as month_created
from dbo.student
where date_created >= '20160101'
and date_created < '20161001'
group by dateadd(month, datediff(month, 0, date_created), 0)
3

Scott, this is great and I bet very simple for you!

If I wanted to format the months in a "mmm-yy" way, how would I force SQL to do that? Example below:
Jan-16 10
Feb-16 15
Mar-16 20
etc.

Thanks so much!

4

No problem!

select sum(number) as number_total, stuff(convert(varchar(11), 
    (dateadd(month, datediff(month, 0, date_created), 0), 0), 4, 6, '-') 
    as month_created
from dbo.student
where date_created >= '20160101'
and date_created < '20161001'
group by dateadd(month, datediff(month, 0, date_created), 0)
5

Thanks so much, Scott. Sadly this isn't working for me. I get an error "Incorrect syntax near ','.", is this because my date format is different to yours? Please note my date format is ''2016-02-01''.

Sorry if this is very simple!

6

If the data type is date or datetime, there are no dashes in the actual data, that's just for display. It's safest to always enter dates as yyyymmdd, with no punctuation, so that the date cannot possibly be misinterpreted.

select sum(number) as number_total, stuff(convert(varchar(11), 
    dateadd(month, datediff(month, 0, date_created), 0), 0), 4, 6, '-') 
    as month_created
from dbo.student
where date_created >= '20160101'
and date_created < '20161001'
group by dateadd(month, datediff(month, 0, date_created), 0)
7

If you don't care about the actual format of the period - you could use this instead:

CONVERT(char(7), date_created, 121)

This will return YYYY-MM and will actually sort appropriately if that is a concern.

8

This is interesting, thanks Jeff! The problem now is that I have multiple for each month. Using the below script, it seems that I am not grouping by the month but by the day I guess?

select convert(char(7),
date_created, 121)
as month_created, count(number) as number_total
from dbo.student
where date_created >= '20160101'
and date_created < '20161001'
group by date_created

Thanks for any advice you can give!

9

You have to group by the same calculation.

10

@jeffw8713 is correct.

select convert(char(7), date_created, 121) as month_created, 
    count(number) as number_total
from dbo.student
where date_created >= '20160101'
and date_created < '20161001'
group by convert(char(7), date_created, 121);
11

Thanks djj55 and jeffw8713! :+1: