Help converting MSSQL query to MYSQL

MYSQL 5.6.22

   Hi,

       I need some help converting a query for use in MYSQL please.  The code below was written for MSSQL and I already have the joins converted for MYSQL.  One problem is that the 'pat_birthdate' field is 'VARCHAR(250) and not a 'DATE' datatype.  I'm not sure how/if this could be affected, but I'm guessing that the query just might 'miss' those rows if they do not have a proper date.

thank you


select s.study_iuid
from pacsdb.patient p
INNER JOIN study s
on p.pk = s.patient_fk
/***where p.pk = s.patient_fk***/
where s.mods_in_study NOT LIKE '%MG%'
/***and s.mods_in_study NOT LIKE 'RTIMAGE'***/
and s.study_datetime IS NOT NULL
and p.pat_birthdate IS NOT NULL
and ISDATE(p.pat_birthdate) = 1
and ISDATE(s.study_datetime) = 1
and CASE WHEN ISDATE(s.study_datetime) = 1 THEN s.study_datetime END
  <= DATEADD(DAY, -2192, GETDATE())
and CASE WHEN ISDATE(p.pat_birthdate) = 1 THEN p.pat_birthdate END
  <= DATEADD(DAY, -7670, GETDATE());

Open in new window

doc_jayAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
MySQL Date arithmetic only works on Date/Datetime columns.  It won't work at all on a VARCHAR column.  And where you have GETDATE(), MySQL has NOW() which is documented here: http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_now  'STR_TO_DATE' is also documented on that page which you may need along with 'SUBDATE' which does date subtraction.

The first problem is to make sure or find a way to get your VARCHAR date into the MySQL Date format.
0
 
Dave BaldwinFixer of ProblemsCommented:
For starters, MySQL does not have an 'ISDATE' function.  Next, the TSQL DATEADD and the MySQL DATE_ADD use different syntax.  

TSQL DATEADD http://msdn.microsoft.com/en-us/library/ms186819.aspx

MySQL DATE_ADD http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add
0
 
doc_jayAuthor Commented:
thanks for your comment.  I'm hoping for a little more help with the 'study_datetime' & the pat_birthdate calculation though.

for 'study_datetime' I'm looking for any row that has a date that is older than 2192 days old or 6 years from today & for the 'pat_birthdate' line, any row that has a birthdate that is older than 7670 days.  This would ensure the patient is older than 21 years.
0
 
doc_jayAuthor Commented:
Thanks for your help on this.  I guess I'll have to put this question on the back burner until I sort out my varchar issue with 'pat_birthdate' column.  I'll ask again once its sorted.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.