SQL 将 YYYYMMDD 转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15317321/
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
Convert YYYYMMDD to DATE
提问by screechOwl
I have a bunch of dates in varcharlike this:
我有一堆这样的日期varchar：
20080107
20090101
20100405
...
How do I convert them to a date format like this:
如何将它们转换为这样的日期格式：
2008-01-07
2009-01-01
2010-04-05
I've tried using this:
我试过用这个：
SELECT [FIRST_NAME]
,[MIDDLE_NAME]
,[LAST_NAME]
,cast([GRADUATION_DATE] as date)
FROM mydb
But get this message:
但是收到这条消息：
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
消息 241，级别 16，状态 1，第 2 行
从字符串转换日期和/或时间时转换失败。
回答by Aaron Bertrand
The error is happening because you (or whoever designed this table) have a bunch of dates in VARCHAR. Why are you (or whoever designed this table) storing dates as strings? Do you (or whoever designed this table) also store salary and prices and distances as strings?
错误的发生是因为您（或设计此表的人）have a bunch of dates in VARCHAR。为什么您（或设计此表的人）将日期存储为字符串？您（或设计此表的人）是否也将薪水、价格和距离存储为字符串？
To find the values that are causing issues (so you (or whoever designed this table) can fix them):
要查找导致问题的值（以便您（或设计此表的人）可以修复它们）：
SELECT GRADUATION_DATE FROM mydb
WHERE ISDATE(GRADUATION_DATE) = 0;
Bet you have at least one row. Fix those values, and then FIX THE TABLE. Or ask whoever designed the table to FIX THE TABLE. Really nicely.
打赌你至少有一行。修复这些值，然后修复表。或者问问设计桌子的人来修理桌子。真的很好。
ALTER TABLE mydb ALTER COLUMN GRADUATION_DATE DATE;
Now you don't have to worry about the formatting - you can always format as YYYYMMDDor YYYY-MM-DDon the client, or using CONVERTin SQL. When you have a validdate as a string literal, you can use:
现在您不必担心格式化 - 您始终可以在客户端上YYYYMMDD或YYYY-MM-DD在客户端上进行格式化，或者CONVERT在 SQL 中使用。当您将有效日期作为字符串文字时，您可以使用：
SELECT CONVERT(CHAR(10), '20120101', 120);
...but this is better done on the client (if at all).
...但这最好在客户端上完成（如果有的话）。
There's a popular term - garbage in, garbage out. You're never going to be able to convert to a date (never mind convert to a stringin a specific format) if your data type choice (or the data type choice of whoever designed the table) inherently allows garbage into your table. Please fix it. Or ask whoever designed the table (again, really nicely) to fix it.
有一个流行的术语——垃圾进，垃圾出。如果您的数据类型选择（或设计表的人的数据类型选择）本质上允许垃圾进入您的表，您将永远无法转换为日期（更不用说转换为特定格式的字符串）。请修复它。或者询问设计桌子的人（再次，非常好）来修复它。
回答by Aaron Bertrand
Use SELECT CONVERT(date, '20140327')
用 SELECT CONVERT(date, '20140327')
In your case,
在你的情况下，
SELECT [FIRST_NAME],
[MIDDLE_NAME],
[LAST_NAME],
CONVERT(date, [GRADUATION_DATE])
FROM mydb
回答by user3590035
In your case it should be:
在你的情况下，它应该是：
Select convert(datetime,convert(varchar(10),GRADUATION_DATE,120)) as
'GRADUATION_DATE' from mydb
