将varchar转换为日期

Question

我（不幸地）有一些日期被保存在varchar列中。这些列包含以下格式的日期：

mmddyy

例如：

010110

我需要将这些值导入到一个设置为datetime的表中，并将日期格式化为以下形式：

2010-09-17 00:00:00.000

如何将上面的字符串转换为下面的datetime值？

- Dave Mackey

---

你的示例可以是二进制（010110）。开玩笑的。 - bobs

鲍勃的哈哈。是的，应该用一个更好的例子。 :) - Dave Mackey

Answer 1

CASE函数可以实现此功能，但是它会默认你的前两位数是年份。下面代码对你有用：

SELECT CAST((RIGHT('010110',2) + LEFT('010110',4)) AS DATETIME)

假设所有日期格式均为MMDDYY。

- Dustin Laine

---

这在一个字面量上运作得很好，但是当我尝试在varchar字段上使用它时，会出现错误：“将varchar数据类型转换为datetime数据类型导致了超出范围的值。” - Dave Mackey

问题是我在所有五位数的日期前面添加了一个0（例如11010而不是011010），但随后进行了回滚 - 因此我试图运行这些错误的字符串，一旦我添加并提交了前导0，一切都正常工作了。谢谢。 - Dave Mackey

Answer 2

这里有一个解决方案

SELECT CAST(SUBSTRING(@date, 5, 2) + SUBSTRING(@date, 1, 4) AS DATETIME)

- bobs

Answer 3

将字符串转化为YYMMDD格式，这样就可以轻松处理了。

declare @x varchar(6)
set @x = '091710'

declare @d datetime

set @d = cast(RIGHT(@x,2) + LEFT(@x,4) as datetime)

select @d

- Joe Stefanelli

Answer 4

支持的日期转换样式在MSDN中有描述。不好的消息是，没有mmddyy的样式。因此，您需要进行自定义格式化。如何完成取决于您的导入方式。它是SSIS ETL步骤吗？还是一次性表格复制？

您可以直接从T-SQL自定义转换指定的格式：

declare @x varchar(6) = '010110';

select dateadd(month, cast(substring(@x, 1,2) as int)-1,
    dateadd(day, cast(substring(@x,3,2) as int)-1,
    dateadd(year, cast(substring(@x,5,2) as int),'01-01-2000')));

- Remus Rusanu

Answer 5

使用substring将相关部分抓取到“yyyy-mm-dd”格式中，然后将其转换为datetime：cast。

cast(
    '20' + substring(col1,5,2) + '-' +
    substring(col1,1,2) + '-' +
    substring(col1,3,2)
    as datetime)

- Andomar

Answer 6

尝试像这样做一些事情：

DECLARE @OldTable table (col1 int, col2 char(1), col3 char(6))
DECLARE @NewTable table (col1 int, col2 char(1), col3 datetime)
INSERT @OldTable VALUES (1,'A','010110') --mmddyy = jan  1, 2010
INSERT @OldTable VALUES (1,'A','091710') --mmddyy = sep 17, 2010

INSERT INTO @NewTable
        (col1, col2, col3)
    SELECT
        col1, col2, RIGHT(col3,2) + LEFT(col3,4) --<< cast to datetime not needed... 
        FROM @OldTable                           --<< because @NewTable.col3 is datetime
        ORDER BY Col1

SELECT * FROM @NewTable

输出：

col1        col2 col3
----------- ---- -----------------------
1           A    2010-01-01 00:00:00.000
1           A    2010-09-17 00:00:00.000

(2 row(s) affected)

- KM.

Answer 7

这将适用于长度为6个字符的varchar字符串

CAST((RIGHT('130513',2) + ltrim(rtrim(right(left('130513', 4), 2))) + LEFT('130513',2) ) AS DATETIME)

这将把字符串转换为2013年5月13日

- Dodgystats