Steve JP (3/26/2013)
...The issue with the convert is that the 3rd arguement is the style and is used to set the style when you convert from a datetime to a varchar, not the other way around....
It works both ways as documented in BOL under CAST and CONVERT (Transact-SQL).
SELECT DateAsCharacter = CONVERT(VARCHAR(25),GETDATE(),100)
-- 'Mar 26 2013 12:49PM'
SELECT DateAsDate = CONVERT(DATETIME,'Mar 26 2013 12:49PM',100)
-- 2013-03-26 12:49:00.000
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden