I think you can also get that "Syntax error..." if the varchar column in the table cannot be converted to a valid date via the CONVERT function.
Try:
Code:
SELECT birthdt, ...
FROM yourtable
WHERE ISDATE(birthdt) = 0
to see if there are any such values.
Your column may contain values like '12/31/2005' and '31/12/2005' which are perfectly legitimate strings, but, depending on how your server is configured, one or the other is always an invalid date. Your column could also contain total junk like 'mm/dd/yyyy', or 'HELLO WORLD'.
You need to find the person who designed this table to have dates stored as a character string, take them out back and shoot them. Next, convert this character string column to a datetime datatype. You'll find that it is much easier to manipulate dates when they are, er, dates.
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com