asked on

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

Dave Baldwin

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

doc_jay

ASKER

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.

ASKER CERTIFIED SOLUTION

Dave Baldwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

doc_jay

ASKER

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.