SQL 日期转换问题。：yyyy/MM/dd 到 dd/MM/yyyy
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9126246/
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
Date conversion issue.: yyyy/MM/dd to dd/MM/yyyy
提问by chamara
I have a date column where the date format is
我有一个日期列，其中日期格式是
2010-04-14
in SQL Server. Is there any possible way to retrieve the date format as
在 SQL Server 中。有没有可能的方法来检索日期格式
14/04/2010
in a select statement?
在选择语句中？
回答by Stuart Blackler
Try this:
尝试这个：
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
Addendum 1:
附录 1：
As described in my comment, you need to convert your field to a DATETIMEtype before the above will work. This example should work on your SQL Server:
如我的评论中所述，您需要将您的字段转换为DATETIME类型，然后才能使用上述方法。此示例应该适用于您的 SQL Server：
SELECT CONVERT(VARCHAR(10), CAST('2010-04-14' AS DATETIME), 103) AS [DD/MM/YYYY]
If you get this exception:
如果您收到此异常：
Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
消息 242，级别 16，状态 3，第 1 行 varchar 数据类型到 datetime 数据类型的转换导致值超出范围。
Then you need to change your date field to be in YYYY-MM-DDFormat, EG: 2010-14-04
然后您需要将日期字段更改为YYYY-MM-DD格式，例如：2010-14-04
OR
或者
Add the following line before the select statement:
在 select 语句之前添加以下行：
SET DATEFORMAT MDY -- Input dates are in the MM/DD/YYYY format, change to DMY to handle UK dates
Example:
例子：
SET DATEFORMAT MDY;
SELECT CONVERT(VARCHAR(10), CAST('2010-04-14' AS DATETIME), 103) AS [DD/MM/YYYY]
回答by aF.
Another way to solve your problem:
解决问题的另一种方法：
select substring(columnName, 9, 2) + '/' + substring(columnName, 6, 2) + '/' + substring(columnName, 1, 4)
回答by Hans Ke?ing
If you are displaying that date outsideof SqlServer, a better way would be to retrieve that date as dateand convert it to the string-representation you want just before displaying it.
如果您在SqlServer之外显示该日期，更好的方法是将该日期作为日期检索，然后在显示之前将其转换为您想要的字符串表示形式。
How to do that depends on your situation.
如何做到这一点取决于你的情况。
回答by Kramned
select replace(datetime,'-','/')
