Hello, I am trying to select records from two tables, all the fields in both tables are varchar's but some of the fields I would like returned as DATETIME types. I have no problem using the CAST(field as DATETIME) function on one table, but as soon as I try to union two selects the CAST no longer functions. Does anyone have any sugestions on how to get around this problem. Here is a simplified query that i cant get to work and the original I simplified from. The main goal of the query is to select all records from both tables where the ID is uinique(it can be repeated in both tables, and I only want one copy of the record). Thank you for any help you might give.
SIMPLIFIED (SELECT CAST(Patients.PatientDateTimeofBirth as DATETIME) AS patBirthDate FROM Patients) UNION (SELECT CAST(Orders.PatientDateTimeofBirth as DATETIME) AS patBirthDate FROM Orders) FULL QUERY ( SELECT Patients.PatientIDInternal AS patID, CAST(Patients.PatientDateTimeofBirth as DATETIME) AS patBirthDate, Patients.PatientIDInternal AS hisUnique FROM Patients LEFT JOIN Orders USING(PatientIDInternal) WHERE Patients.Imported = 1) UNION (SELECT Orders.PatientIDInternal AS patID, CAST(Orders.PatientDateTimeofBirth as DATETIME) AS patBirthDate, Orders.PatientIDInternal AS hisUnique FROM Orders LEFT JOIN Patients USING(PatientIDInternal) WHERE Patients.PatientIDInternal IS NULL AND Orders.PatientImported = 1) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
