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

js4nwp54  于 2023-10-15  发布在  其他
关注(0)|答案(3)|浏览(95)

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!

weylhg0b

weylhg0b1#

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

pepwfjgg2#

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

nfs0ujit3#

Adding to your query, you can just add the Zeros/the characters as you want :)...

SELECT CONVERT(VARCHAR (10),'7/19/2013',120) + ' 00:00:00.000'

Result:

7/19/2013 00:00:00.000

相关问题