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

    Join Date
    Jun 2008
    Posts
    125
    Rep Power
    10

    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,407
    Rep Power
    4286
    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
    125
    Rep Power
    10
    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,407
    Rep Power
    4286
    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
    125
    Rep Power
    10
    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,407
    Rep Power
    4286
    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
    125
    Rep Power
    10
    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,407
    Rep Power
    4286
    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
    125
    Rep Power
    10
    Originally Posted by r937
    bingo

    why dontcha test the second one and find out

IMN logo majestic logo threadwatch logo seochat tools logo