1

I am using Microsoft SQL Database Management Studio and it will not allow me to use the strftime() function to run a query. I have to create a table by months with new users and unsubscribers for each month.

This is what I had essentially which creates the error:

SELECT strftime('%m', createddate) AS 'Month', COUNT(createddate) AS 'Subscribers',
       COUNT(dateunsubscribed) AS 'UNsubscribers'
FROM subscriber
GROUP BY 1
ORDER BY 1;

how else could I run this query without strftime() or how can I get strftime() to work?

Mureinik
  • 252,575
  • 45
  • 248
  • 283
Klew
  • 33
  • 1
  • 3
  • That's because `strftime()` isn't a SQL Server function. Hint: Look at [formats](https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-2017) instead – Widor Aug 14 '18 at 15:49

1 Answers1

3

strftime is a function, and isn't available in Microsoft's .

For this simple usecase (extracting a month from a date), you could user month:

SELECT   MONTH(createddate) AS [Month], 
         COUNT(createddate) AS [Subscribers],
         COUNT(dateunsubscribed) AS [UNsubscribers]
FROM     subscriber
GROUP BY 1
ORDER BY 1;

EDIT:
To address the question in the comment, the group by clause doesn't take an ordinal like the order by clause does. You'll need to specify the expression you want to group by:

SELECT   MONTH(createddate) AS [Month], 
         COUNT(createddate) AS [Subscribers],
         COUNT(dateunsubscribed) AS [UNsubscribers]
FROM     subscriber
GROUP BY 1
ORDER BY MONTH(createddate);
Mureinik
  • 252,575
  • 45
  • 248
  • 283
  • I keep receiving this error now: Each GROUP BY expression must contain at least one column that is not an outer reference. – Klew Aug 14 '18 at 17:24
  • @Klew that's a different issue. See my updated answer for a solution. – Mureinik Aug 14 '18 at 18:25