T-SQL 2008 将日期和时间字符串转换为日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15302294/
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
T-SQL 2008 Convert Date and time string to datetime
提问by Dan Neville
I have two columns in my table, one to capture time and one to capture date. Unfortunately, both are varchar(). I need to take the two fields, concatenate them together, and then convert them to datetime.
我的表中有两列，一列用于捕获时间，一列用于捕获日期。不幸的是，两者都是 varchar()。我需要取两个字段，将它们连接在一起，然后将它们转换为日期时间。
I am trying to accomplish that with this:
我正在尝试通过以下方式实现这一目标：
select CONVERT(datetime,(select txt_returned_date+' '+CONVERT(varchar(20),CONVERT(TIME,txt_time_returned))),126)
from table_name
I am getting this error message:
我收到此错误消息：
Conversion failed when converting date and/or time from character string.
The date is being captured as "20130308" as a string. Time is being captures as "4:27 PM" as a string
日期被捕获为“20130308”作为字符串。时间被捕获为“4:27 PM”作为字符串
What I am doing here is converting the string of the time to TIME, then back to varchar. Then I am concatenating them together. This works by itself, but once I introduce the CONVERT(datetime) to the whole query, it is giving me the error.
我在这里做的是将时间字符串转换为 TIME，然后再转换回 varchar。然后我将它们连接在一起。这本身就有效，但是一旦我将 CONVERT(datetime) 引入整个查询，它就会给我错误。
Any help to try to accomplish this is helpful. Thanks!
任何尝试实现这一目标的帮助都是有帮助的。谢谢！
回答by sqlfool
You can concatenate the DATE and TIME values together once they have been converted to a DATETIME. Here's a sample to play with that shows concatenating a DATE column and a TIME column that have been stored as VARCHAR:
将 DATE 和 TIME 值转换为 DATETIME 后，您可以将它们连接在一起。下面是一个示例，显示连接已存储为 VARCHAR 的 DATE 列和 TIME 列：
-- Set up some variables to test with
DECLARE @myTime TIME = GETDATE()
, @myDate DATE = GETDATE()
, @myTimeTxt VARCHAR(16)
, @myDateTxt VARCHAR(10);
-- Initialize your variables
SELECT @myTimeTxt = @myTime
, @myDateTxt = @myDate;
-- Display your separated values
SELECT @myDateTxt, @myTimeTxt;
-- Display your concatenated value
SELECT CAST(@myDateTxt AS DATETIME) + CAST(CAST(@myTimeTxt AS TIME) AS DATETIME);
回答by Aleksandr Fedorenko
You can use this option
您可以使用此选项
DECLARE @date date = '20010101',
@time time = '01:01:01'
SELECT CAST(@date AS datetime) + @time
Result:2001-01-01 01:01:01.000
结果：2001-01-01 01:01:01.000
Demo on SQLFiddle
回答by steoleary
Are you using SQL 2012? If so you may be able to use the datetimedromparts function to achieve this. If not for this specific example, it's always good to know for the future :)
您在使用 SQL 2012 吗？如果是这样，您可以使用 datetimedromparts 函数来实现这一点。如果不是这个特定的例子，那么了解未来总是好的:)
