It seems like an easy thing to convert a sting to a datetime in SQL Server. I check the MSDN documentation of converting between strings (varchar / nvarchar) to a DateTime datatype. The problems that I ran into is that when I look at the predefined formats that it understands, none of them seem to be mm/dd/yyyy hh:mi:ss:mmm. The only one that is even close is 101 which is U.S. and makes sense. I was a bit surprised when I tried it that it actually automatically recognized the time portion as well. I was very pleased. So, in fact, I thought it should be easy, it looked like it would be difficult, and in the end it is easy if you know the trick.
Here is all I did.
select convert(datetime, '02/22/2010 16:01:51.802', 101)
That returns 2010-02-22 16:01:51.803 (a datetime datatype)
Keep in mind, I used a literal, but you could use any column that is a varchar or nvarchar and contains a string of that format.
NOTE: I have no idea why the last digit is 803 vs 802. It is not a typo on my part. It is actually accurate. So strange. Bug in SQL Server maybe?
Tip: If you happen to need to convert mm-dd-yyyy hh:mi:ss:mmm, you can just use the replace function to change it to the above format.
select convert(datetime, Replace('02-22-2010 16:01:51.802', '-', '/'), 101)
No comments:
Post a Comment