The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> MySQL Help
|
MSSQL to MYSQL conversion
Discuss MSSQL to MYSQL conversion in the MySQL Help forum on Dev Shed.
MSSQL to MYSQL conversion MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS).
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
May 8th, 2012, 02:27 PM
|
|
Contributing User
|
|
Join Date: Jun 2008
Posts: 119
Time spent in forums: 1 Day 17 h 16 m 15 sec
Reputation Power: 7
|
|
|
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.
|
May 8th, 2012, 03:36 PM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
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
|
May 8th, 2012, 04:32 PM
|
|
Contributing User
|
|
Join Date: Jun 2008
Posts: 119
Time spent in forums: 1 Day 17 h 16 m 15 sec
Reputation Power: 7
|
|
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.
|
May 8th, 2012, 05:15 PM
|
|
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
Quote: | 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!
|
May 8th, 2012, 06:51 PM
|
|
Contributing User
|
|
Join Date: Jun 2008
Posts: 119
Time spent in forums: 1 Day 17 h 16 m 15 sec
Reputation Power: 7
|
|
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.
|
May 8th, 2012, 07:28 PM
|
|
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
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?
|
May 8th, 2012, 08:52 PM
|
|
Contributing User
|
|
Join Date: Jun 2008
Posts: 119
Time spent in forums: 1 Day 17 h 16 m 15 sec
Reputation Power: 7
|
|
Quote: | 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 count( ada_date )
FROM psc_addons
WHERE YEARWEEK( ada_date ) = YEARWEEK( CURRENT_DATE - INTERVAL 7
DAY )
Last edited by gmrstudios : May 8th, 2012 at 09:06 PM.
|
May 8th, 2012, 09:06 PM
|
|
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
Quote: | Originally Posted by gmrstudios Couldn't I achieve the same goal doing... | bingo
why dontcha test the second one and find out
|
May 8th, 2012, 09:07 PM
|
|
Contributing User
|
|
Join Date: Jun 2008
Posts: 119
Time spent in forums: 1 Day 17 h 16 m 15 sec
Reputation Power: 7
|
|
Quote: | Originally Posted by r937 bingo
why dontcha test the second one and find out |
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
