SQL Server 将 Varchar 转换为日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10247050/
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 Server Convert Varchar to Datetime
提问by HaOx
I have this date format: 2011-09-28 18:01:00 (in varchar), and I want to convert it to datetime changing to this format 28-09-2011 18:01:00. How can I do it?
我有这个日期格式：2011-09-28 18:01:00（在 varchar 中），我想将它转换为日期时间更改为这种格式 28-09-2011 18:01:00。我该怎么做？
回答by Zyku
SELECT CONVERT(Datetime, '2011-09-28 18:01:00', 120) -- to convert it to Datetime
SELECT CONVERT( VARCHAR(30), @date ,105) -- italian format [28-09-2011 18:01:00]
+ ' ' + SELECT CONVERT( VARCHAR(30), @date ,108 ) -- full date [with time/minutes/sec]
回答by zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
Like this
像这样
DECLARE @date DATETIME
SET @date = '2011-09-28 18:01:00'
select convert(varchar, @date,105) + ' ' + convert(varchar, @date,108)
回答by Diego
回答by baymax
You can have all the different styles to datetime conversion:
您可以拥有所有不同的样式datetime conversion：
http://www.w3schools.com/sql/func_convert.asp
http://www.w3schools.com/sql/func_convert.asp
This has range of values :-
这具有值范围：-
CONVERT(data_type(length),expression,style)
For style values,
Choose anyone you need like I needed 106.
对于样式值，请
选择您需要的任何人，就像我需要 106 一样。
回答by Pankaj Agarwal
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) + ' ' + CONVERT(VARCHAR(10), GETDATE(), 108)
回答by Paddy
As has been said, datetime has no format/string representational format.
如前所述，日期时间没有格式/字符串表示格式。
You can change the string output with some formatting.
您可以使用某种格式更改字符串输出。
To convert your string to a datetime:
要将字符串转换为日期时间：
declare @date nvarchar(25)
set @date = '2011-09-28 18:01:00'
-- To datetime datatype
SELECT CONVERT(datetime, @date)
Gives:
给出：
-----------------------
2011-09-28 18:01:00.000
(1 row(s) affected)
To convert that to the string you want:
要将其转换为您想要的字符串：
-- To VARCHAR of your desired format
SELECT CONVERT(VARCHAR(10), CONVERT(datetime, @date), 105) +' '+ CONVERT(VARCHAR(8), CONVERT(datetime, @date), 108)
Gives:
给出：
-------------------
28-09-2011 18:01:00
(1 row(s) affected)
回答by Taryn
You could do it this way but it leaves it as a varchar
你可以这样做，但它把它作为一个varchar
declare @s varchar(50)
set @s = '2011-09-28 18:01:00'
select convert(varchar, cast(@s as datetime), 105) + RIGHT(@s, 9)
or
或者
select convert(varchar(20), @s, 105)
回答by Bala
Try the below
试试下面的
select Convert(Varchar(50),yourcolumn,103) as Converted_Date from yourtbl
