-1

I have an invoice date column in my table which contains data as below

20150131
20150231
20160128
20161231

I want to group the data based on the year of the transaction... i.e 2015 or 2016. My column type is nvarchar.

Can someone please help. I am using SQL Server 2012.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Newbie
  • 713
  • 2
  • 10
  • 19
  • 1
    Possible duplicate of [How do I do \`group by\` partial match](http://stackoverflow.com/questions/5977506/how-do-i-do-group-by-partial-match) – CodeCaster Jul 03 '16 at 12:07
  • Or if they're actually datetimes: [How to Group by Year](http://stackoverflow.com/questions/5964094/how-to-group-by-year) – CodeCaster Jul 03 '16 at 12:08
  • 3
    Read Aaron Bertrand's [Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) – Zohar Peled Jul 03 '16 at 12:11
  • Can you show the result you need? – ɐlǝx Jul 03 '16 at 12:20
  • I think you want to filter your data not group it when you said 2015 or 2016, if yes; for 2015 use `WHERE date BETWEEN 20150000 AND 20160000` ;). – shA.t Jul 03 '16 at 12:21

3 Answers3

1

Assuming that the column is integer, since integer division in SQL Server truncates the result, all you need to do is dividing out the lower four digits:

SELECT
    datefield/10000 AS Year
,   SUM(...) AS ...
FROM MyTable
WHERE ...
GROUP BY datefield/10000

If your column is a char or varchar, use LEFT(datefield,4) instead.

Note: I assume that you do not own the table, so switching to a more appropriate type (i.e. date) is not an option. Otherwise, I would strongly advise a switch, because date is smaller, and it also gives you access to date-specific functionality, such as extracting the day, the month, and the year.

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • 4
    That's assuming the data type is integer... if it's char then it should be using `LEFT(datefield, 4)` instead. – Zohar Peled Jul 03 '16 at 12:10
  • @ZoharPeled I am nearly certain that it's integer, because I've seen other people doing it for convenience instead of using `date`. It is hard to imagine why one would pick a string representation for a date, though, because it's larger and slower. – Sergey Kalinichenko Jul 03 '16 at 12:14
  • 2
    I tend to agree with your assumption, however I've seen my share of coding horrors. I've learned not to assume the obvious in these matters. So when in doubt, I usually ask or show both possibilities. – Zohar Peled Jul 03 '16 at 12:17
  • @Newbie So it *is* a string then. That's probably the worst of choice of the three possibilities. If you can convert your table to use `date` instead, I strongly advise you to do so. Add a `date` column, run an update to transfer the content from strings to date, then drop the string column. – Sergey Kalinichenko Jul 03 '16 at 12:35
1

If your dates are valid, then simply you can use this:

SELECT ... FROM ... GROUP BY YEAR(CAST(myDate AS DATETIME))

But if your dates are not valid(as is now, because February has 28 days), you can try this:

SELECT ... FROM ... GROUP BY SUBSTRING(myDate,1,4)

Or

SELECT ... FROM ... GROUP BY Left(myDate,4)
Vahid Farahmandian
  • 6,081
  • 7
  • 42
  • 62
0

you can use datepart function;

select DATEPART(year,CONVERT(date,col,105)),COUNT(*) from table_1
group by DATEPART(year,CONVERT(date,col,105))
Psidom
  • 209,562
  • 33
  • 339
  • 356
hitechnique
  • 11
  • 2