sqlsql-serverdate

Format date as yyyy-mm-dd hh:mm:ss.000


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!


Solution

  • 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);