Convert a MySQL date based SELECT statement to MSSQL

Databases
1

Hi,

I’m converting a MySQL site to MSSQL and I’m a bit stuck on date parts, etc. This SELECT statement creates a menu/navigation of “months” with the number of posts after each month (it only lists months where there was a post), i.e:

July 2013 (3 posts)
May 2013 (2 posts)
April 2013 (4 posts)

It works great in MySQL but the bits of the statement which break down the date into elements isn’t working in MSSQL 2005 (I have replaced the statements with theri MSSQL equivilants but it still doesn’t work.

In this example blgdate is the date of the post stored in the YYYY-MM-DD format (as a DATE). The database table is tblblogarticles

This is my working MySQL statement:

"SELECT YEAR(blgdate) AS yr, MONTH(blgdate) AS mth, DATE_FORMAT(blgdate,'%M %Y') AS display_date, COUNT(*) AS countnum FROM tblblogarticles WHERE YEAR(blgdate) = YEAR(CURRENT_DATE) AND blgenabled = 'Y' GROUP BY yr,mth ORDER BY yr DESC, mth DESC"

This is what I’ve tried with MSSQL 2005:

"SELECT DATEPART(yyyy,blgdate) AS yr, DATEPART(mm,blgdate) AS mth, CONVERT(blgdate,'%M %Y') AS display_date, COUNT(*) AS countnum FROM tblblogarticles WHERE DATEPART(yyyy,blgdate) = DATEPART(yyyy,GETDATE()) AND blgenabled = 'Y' GROUP BY yr,mth ORDER BY yr DESC, mth DESC"

When I try that code I get this error:

Microsoft SQL Native Client error ‘80040e14’
Invalid column name ‘yr’.

Can anyone help please.

Many thanks.
Regards,
Adrian

2

I can see three problems in your MSSQL statement.

  1. The first parameter to convert is incorrect, it should be the name of a data type. E.g. convert(varchar(20),blgdate). If you want the actual name of the month the function datename is more apropriate.

  2. Column correlation names (e.g. yr) can not be used in a group by clause nor in a having clause. (That functionality in Mysql is a standard deviation.) There are different ways to resolve this.
    You can either repeat the expressions in the select list in the group by clause.
    You can use a derived table which contains the expressions e.g.


select yr,mth,count(*)
  from (select datepart(yyyy,blgdate) as yr, ...) dt
 group by yr,mth

There is also a with clause in MSSQL that can be used for this purpose.

  1. MSSQL requires that all columns in the select list that are not aggregated, must be present in the group by clause.
3

Hi,

Thanks for the advice.

I understand the first point and have amended the SELECT statement accordingly but I don’t understand the GROUP BY part.

If I use this statement:

"SELECT blgdate, DATEPART(yyyy,blgdate) AS yr, DATEPART(mm,blgdate) AS mth, (DATENAME (mm,blgdate) + ' ' + DATENAME (yy,blgdate)) AS display_date, COUNT(*) AS countnum FROM tblblogarticles WHERE DATEPART(yyyy,blgdate) = DATEPART(yyyy,GETDATE()) AND blgenabled = 'Y' GROUP BY blgdate ORDER BY blgdate DESC"

The recordset is returned without errors but I, obviously, get a separate entry for each “article” whereas I want the results to be “grouped” by year and date (so there is one entry for each month). I understand I can’t use aggregated names in the GROUP BY clause but if I try using the expressions form the select statement I get an error:

GROUP BY DATEPART(yyyy,blgdate), DATEPART(mm,blgdate)

I’m a bit stuck.

4

Including blgdate in the select list when grouping on year and month does not make sense. Which date do you expect to retrieve?

I would remove blgdate from the select list and use the following code


SELECT DATEPART(yyyy,blgdate) AS yr,
       DATEPART(mm,blgdate) AS mth,
       DATENAME (mm,blgdate) + ' ' + DATENAME (yy,blgdate) AS display_date,
       COUNT(*) AS countnum
  FROM tblblogarticles 
 WHERE DATEPART(yyyy,blgdate) = DATEPART(yyyy,GETDATE()) 
   AND blgenabled = 'Y' 
 GROUP 
    BY DATEPART(yyyy,blgdate),
       DATEPART(mm,blgdate),
       DATENAME (mm,blgdate) + ' ' + DATENAME (yy,blgdate)
 ORDER
    BY yr desc,
       mt desc

If you get an error when executing a statement, please include the complete error message in your post. Also when posting a statement it is better if you split it over several lines, thereby abating the need for horisontal scrolling.

5

this might as well say “all databases i have ever heard of, except mysql, require that…”

mysql’s behaviour has caused, in my opinion, more damage to database developers than any benefit it was supposed to have

only those who know what it was supposed to do (not requiring columns that don’t vary across the grouped columns to be in the GROUP BY) will ever take advantage of this feature successfully

relevant article, long but worth the effort – Debunking GROUP BY myths

6

Fantastic - thanks so much for that. It works a treat. Where I was going wrong was not including the “display_date” expression in the group by clause.

Thanks again for all your help.

I now have another conversion (MySQL to MSSQL) problem on another page but I’ll post a new question about that.

Regards,
Adrian