Supported by SQL Server 2005 and later versions
SELECT CONVERT(VARCHAR(10), GETDATE(), 101)
+ ' ' + CONVERT(VARCHAR(8), GETDATE(), 108)
* See Microsoft's documentation to understand what the 101 and 108 style codes above mean.
Supported by SQL Server 2012 and later versions
SELECT FORMAT(GETDATE() , 'MM/dd/yyyy HH:mm:ss')
Result
Both of the above methods will return:
10/16/2013 17:00:20
Supported by SQL Server 2005 and later versions
SELECT CONVERT(VARCHAR(10), GETDATE(), 101)
+ ' ' + CONVERT(VARCHAR(8), GETDATE(), 108)
* See Microsoft's documentation to understand what the 101 and 108 style codes above mean.
Supported by SQL Server 2012 and later versions
SELECT FORMAT(GETDATE() , 'MM/dd/yyyy HH:mm:ss')
Result
Both of the above methods will return:
10/16/2013 17:00:20
Try below:
SELECT CONVERT(VARCHAR(20), GETDATE(), 101)
Date Format (mm/dd/yyyy hh:mm), no seconds or milliseconds – SQLServerCentral Forums
Datetime conversion Format dd/mm/yyyy hh:mm:ss AM - SQL Server Forums
sql - Format date as yyyy-mm-dd hh:mm:ss.000 - Stack Overflow
Date and time format not recognized (MMM DD, YYYY, HH:MM:SS AM/PM)
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);
Note that Aaron's solution will fail if the server is localized to a language with DMY as the date format. This is because the inner CONVERT in Aaron's example will incorporate the server locale, which may not be what you expect.
To make this bulletproof (assuming the source of the string doesn't automatically re-localize the format), convert the string with PARSE (requires SQL Server 2012 or later).
SET LANGUAGE English
SELECT CONVERT(CHAR(23),TRY_CONVERT(DATETIME,'7/19/2013'),121);
SELECT CONVERT(CHAR(23),PARSE('7/19/2013' AS DATETIME USING 'en-US'),121);
SET LANGUAGE Français
SELECT CONVERT(CHAR(23),TRY_CONVERT(DATETIME,'7/19/2013'),121);
SELECT CONVERT(CHAR(23),PARSE('7/19/2013' AS DATETIME USING 'en-US'),121);