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!
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!
3条答案
按热度按时间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 asm/d/y
regardless of locale, but if you're on SQL Server 2012 you could also usePARSE()
as in his example (orTRY_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.
Result:
If you don't care about milliseconds, you can use style 120 instead:
And if you don't care about seconds, you can truncate earlier:
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).
nfs0ujit3#
Adding to your query, you can just add the Zeros/the characters as you want :)...
Result: