迄今为止的 SQL VarChar
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4793311/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL VarChar to Date
提问by Shariful
hi i am trying to convert a VarChar date field (e.g. 20100320) to a real date field like
嗨，我正在尝试将 VarChar 日期字段（例如 20100320）转换为真实的日期字段，例如
'dd/mm/yyyy' (e.g. 20/03/2010).
'dd/mm/yyyy' (e.g. 20/03/2010).
I have tried two ways: a)
我尝试了两种方法：a）
(SELECT MIN(CAST(A.DateOfAction AS Date)) AS Expr1
FROM ResAdm.Action A
WHERE (A.PersonID = P.PersonID))
AS 'Period From',
b)
b)
(SELECT MIN(CONVERT(DATE, A.DateOfAction, 103)) AS Expr1
FROM ResAdm.Action A
WHERE (A.PersonID = P.PersonID))
AS 'Period From',
both producing the result like
两者都产生像
yyyy-mm-dd (e.g. 2010-03-20)
yyyy-mm-dd（例如 2010-03-20）
but i want the result like
但我想要这样的结果
dd/mm/yyyy (e.g. 20/03/2010)
日/月/年（例如 20/03/2010）
any help will be appreciated. thanks.
任何帮助将不胜感激。谢谢。
回答by Jon
Try this:
尝试这个：
select convert(varchar(8), convert(datetime, min(a.DateOfAction), 112), 103)
Your problem is that once you have a date format, SQL Server will dump it out in its default date format, which you've discovered is yyyy-mm-dd. You need to convert from dateto varcharto get the format you want. But to convert from date, you need to first convert to date! 112 is the format for yyyymmdd, and 103 is the format for dd/mm/yyyy, so this is why you need these formats. (Books Online reference for date formats)
您的问题是，一旦您有了日期格式，SQL Server 就会以其默认日期格式将其转储出来，您发现该格式是yyyy-mm-dd. 您需要从 转换date为varchar您想要的格式。但是要从日期转换，您需要先转换为日期！112 是 的格式yyyymmdd，103 是 的格式dd/mm/yyyy，所以这就是您需要这些格式的原因。（有关日期格式的联机丛书参考）
回答by ashish.chotalia
Declare @date nvarchar(100)
set @date = '20100320'
select convert(varchar, CONVERT(datetime, @date, 109), 103)
You can use
您可以使用
convert(varchar, CONVERT(datetime, A.DateOfAction, 109), 103)
