I have this date: 7/19/2013
I want to format it as the following:
2013-07-19 00:00:00.000
I tried this:
select convert(varchar(10),'7/19/2013',120)
But it is giving me the same result!
You need to tell SQL Server it's a date; otherwise, it just sees a string, and ignores the style number since it's not relevant for a string. As Steve Kass pointed out, the code is only truly portable if you protect the incoming string from incorrect regional- or language-based translations (such as d/m/y - which could lead to an error or, even worse, the wrong data). I've updated the code to interpret the string as m/d/y regardless of locale, but if you're on SQL Server 2012 you could also use PARSE() as in his example (or TRY_PARSE() if you want to essentially ignore invalid dates).
And if you want the time attached including milliseconds, you need to allow more than 10 characters, and a style that supports milliseconds.
SELECT CONVERT(CHAR(23),CONVERT(DATETIME,'7/19/2013',101),121);
Result:
2013-07-19 00:00:00.000
If you don't care about milliseconds, you can use style 120 instead:
SELECT CONVERT(CHAR(19),CONVERT(DATETIME,'7/19/2013',101),120);
And if you don't care about seconds, you can truncate earlier:
SELECT CONVERT(CHAR(16),CONVERT(DATETIME,'7/19/2013',101),120);