• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

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

0
doc_jay
Asked:
doc_jay
  • 2
  • 2
1 Solution
 
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
 
Dave BaldwinFixer 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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now