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.
...
ChrisM@Work (3/27/2013)
....
The style argument for converting a date as string in the format 'Mar 26 2013 12:49PM' to a datetime data type using CONVERT() is 100. Other style arguments are likely to fail. I'm sorry but I still don't see your point. None of this appears to prove anything either way. Have a quick read of CAST and CONVERT in BOL, specifically CONVERT relating to dates. The explanation is quite good. If you're still unsure, post back......
Chris, I thought my example was clear in that the style code ( of 25510 ) that I first used doesnt exist. I know this & you know this, but others may not, as this is the newbies section hence I posted the code. The next code block shows that the Style arguement is often ignored for a conversion from varchar to datetime. It also doesnt have any affect on the time. ie the 103 (which is English) will take a datetime to a varchar as only a date of format dd/mm/yyyy. But from a varchar to datetime will take the time element if it exists as well so can be a bit misleading for some. How many times have people asked how to truncate the time element from a datetime value.
If the style arguement doest exist but cant be applied then it will error. Other times like I have shown it will ignore the style arguement if the actual data contradicts the style.
One of the most reliable methods I have found to enter datetime is yyyy-mm-dd hh:mm:ss:mmm (or the full iso style)
Another option is to use SET DATEFORMAT, although I'm not certain what lifespan this has long term.
I havent got all day for these forums and can only post while jobs are running etc. So yes some of the time my comments are a bit too brief, but mainly I try and post a bit more than "go read the BOLs".