SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    convert a MySQL date based SELECT statement to MSSQL

    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:

    Code:
    "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:

    Code:
    "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. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    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.

    Code:
    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.

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

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    "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:

    Code:
    GROUP BY DATEPART(yyyy,blgdate), DATEPART(mm,blgdate)
    I'm a bit stuck.

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    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

    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. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by swampBoogie View Post
    3) MSSQL requires that all columns in the select list that are not aggregated, must be present in the group by clause.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •