#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9

    MSSQL to MYSQL conversion


    Hello and thanks for reading. I had to rewrite a query after moving from a MSSQL to MYSQL and wanted to see if my SYNTAX was correct. While it looks like it is pulling the same data I wanted to run it by this fine forum to see if I am using the proper functions, etc.

    MSSQL
    PHP Code:
    SELECT datepart(wk,modified_on) = datepart(wk,dateadd(d,-7,getdate())) 
    AND 
    year(modified_on) = year(dateadd(d,-7,getdate())) 
    Converted to...

    PHP Code:
    SELECT DATE_FORMAT'ada_date''%W' ) = DATE_FORMAT'date_sub(CURDATE(), INTERVAL 7 DAY)''%W' 
    AND 
    YEAR'ada_date' ) = YEAR'date_sub(CURDATE(), INTERVAL 7 DAY)' 
    Last edited by gmrstudios; May 8th, 2012 at 02:30 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,151
    Rep Power
    4274
    i have a problem understanding what your mssql query was doing

    perhaps you copy/pasted it incorrectly?

    Code:
    SELECT datepart(wk,modified_on) = datepart(wk,dateadd(d,-7,getdate()))
    why are you equating the two weeks?

    are you hoping to get a TRUE/FALSE result?

    does mssql return 0/1 instead?

    in any case, you have some problems with your mysql functions

    for example, here --
    Code:
    DATE_FORMAT( 'ada_date', '%W' )
    you're trying to get the week number of a string

    [sigh]... another developer is pwned by mysql's evil backticks

    remove the single quotes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9
    Ooops! My apologizes this is the actual query:

    PHP Code:
    $result_lw=mysql_query("SELECT ada_date
    FROM psc_addons
    WHERE ada_date >= DATE_FORMAT('ada_date','%W') = DATE_FORMAT('date_sub(CURDATE(), INTERVAL 7 DAY)','%W') AND YEAR('ada_date') = YEAR('date_sub(CURDATE(), INTERVAL 7 DAY)')"
    )
    or die (
    'Query failed: ' mysql_error() . '');
    $num_rows_lw mysql_num_rows($result_lw); 
    I am attempting to pull the total amount of records from the previous week. I then do a mysql_num_rows() on the result.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,151
    Rep Power
    4274
    Originally Posted by gmrstudios
    Ooops! My apologizes this is the actual query:
    sorry, but i just don't believe it

    you have this --

    Code:
    WHERE ada_date >= DATE_FORMAT('ada_date','%W') = DATE_FORMAT('date_sub(CURDATE(), INTERVAL 7 DAY)','%W')
    in other words, WHERE a = b = c

    that's just plain invalid (well, actually, mysql will execute it, but it will always be false)

    and by the way, you're still trying to get the week number of a string -- get rid of those quotes!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9
    Humm... So this was my original MSSQL query. It works just fine over in MSSQL land (I no longer need the status_lookup_id portion in the new query).

    PHP Code:
    SELECT number     
    FROM workitem
    WHERE status_lookup_id 
    600 
    AND 
    datepart(wk,modified_on) = datepart(wk,dateadd(d,-7,getdate())) 
    AND 
    year(modified_on) = year(dateadd(d,-7,getdate())) 
    Which is how I came up with...

    PHP Code:
    SELECT ada_date     
    FROM psc_addons
    WHERE
    DATE_FORMAT
    ('ada_date','%W') = DATE_FORMAT('date_sub(CURDATE(), INTERVAL 7 DAY)','%W')
    AND 
    YEAR('ada_date') = YEAR('date_sub(CURDATE(), INTERVAL 7 DAY)'
    When I remove the ' from the second query MySQL seems to get pissed. With the goal of all of this being to pull all records with a ada_date (2000-00-00 00:00:00 is the format) from the previous week.
    Last edited by gmrstudios; May 8th, 2012 at 06:56 PM.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,151
    Rep Power
    4274
    can you understand my confusion? did you notice that the WHERE a = b = c disappeared in your last post?

    let me try a third time to point out the problem

    this --
    Code:
    DATE_FORMAT('ada_date','%W')
    is trying to get the week number of a string

    this --
    Code:
    DATE_FORMAT(ada_date,'%W')
    gets the week number of a column

    with me now?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9
    Originally Posted by r937
    can you understand my confusion? did you notice that the WHERE a = b = c disappeared in your last post?

    let me try a third time to point out the problem

    this --
    Code:
    DATE_FORMAT('ada_date','%W')
    is trying to get the week number of a string

    this --
    Code:
    DATE_FORMAT(ada_date,'%W')
    gets the week number of a column

    with me now?
    Dang! Yes, I am with you now.

    After actually looking at this with new eyes. Couldn't I achieve the same goal doing:
    PHP Code:
    SELECT FROM psc_addons
    WHERE YEARWEEK
    (ada_date) = YEARWEEK(CURRENT_DATE INTERVAL 7 DAY
    OR
    PHP Code:
    SELECT countada_date 
    FROM psc_addons
    WHERE YEARWEEK
    ada_date ) = YEARWEEKCURRENT_DATE INTERVAL 7 
    DAY 

    Last edited by gmrstudios; May 8th, 2012 at 09:06 PM.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,151
    Rep Power
    4274
    Originally Posted by gmrstudios
    Couldn't I achieve the same goal doing...
    bingo

    why dontcha test the second one and find out
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9
    Originally Posted by r937
    bingo

    why dontcha test the second one and find out

IMN logo majestic logo threadwatch logo seochat tools logo