to_date in SQL Server 2005

Question

Does any one know how I would have to change the following to work with ms sql?

WHERE registrationDate between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');

What I have read implies I would have to construct it using DATEPART() which could become very long winded. Especially when the goal would be to compare on dates which I receive in the following format "2003-12-30 10:07:42". It would be nice to pass them off to the database as is.

Any pointers appreciated.

Answer 1

Use:

WHERE registrationdate BETWEEN '01/01/2003' AND '12/31/2003'

...but as gbn pointed out, to be locale safe - use:

WHERE registrationdate BETWEEN '20030101' AND '20031231'

SQL Server will perform implicit conversion of the string into a date, providing it's a supported format. Explicit conversion is when you have to use CAST or CONVERT to change the data type.

When converting '01/01/2003' to a DATETIME, the time portion will be 00:00:00 because it wasn't specified.