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.