将nvarchar转换为datetime

Question

我们有一个表，其中包含数据类型为nvarchar(254)的列。此列以以下格式存储日期和时间：

12-MAR-15 04.07.36.000000000 PM

我们需要将其转换为datetime格式，以便运行与时间相关的查询来提取所需的结果。该数据未以CAST和CONVERT文档页面中提到的任何格式存储

我尝试了130和131，但没有成功。

这里的格式似乎是dd-mon-yy hh.mm.ss.mmmmmmm PM

有人知道我们应该如何将其转换为"日期时间"格式吗？

任何帮助都将不胜感激。

Answer 1

首先，将日期存储为字符串是个坏主意。这是使用STUFF和REPLACE函数将它们转换回日期时间的一种方法。

我想你所有的约会都是在2000年之后。

declare @d nvarchar(256) = '12-MAR-15 04.07.36.000000000 PM'
SELECT CONVERT(DATETIME, 
        STUFF(
         REPLACE(REPLACE(
          STUFF(@d, 23, 7, ''), '-',' '),'.',':'), 8,0,'20')  )

Answer 2

基本上，您需要将日期字符串转换为SQL Server支持的样式之一。在以下示例中，我的目标日期样式109(mon dd yyyy hh:mi:ss:mmmAM(:

-- character position                0        1         2
--                                   1234567890123456789012345678
DECLARE @DATESTR AS NVARCHAR(254) = '12-MAR-15 04.07.36.000000000 PM'
SELECT CONVERT(DATETIME, 
    SUBSTRING(@DATESTR,  4, 3) + ' ' + -- mon
    SUBSTRING(@DATESTR,  1, 2) + ' ' + -- dd
    SUBSTRING(@DATESTR,  8, 2) + ' ' + -- yy
    SUBSTRING(@DATESTR, 11, 2) + ':' + -- hh
    SUBSTRING(@DATESTR, 14, 2) + ':' + -- mi
    SUBSTRING(@DATESTR, 17, 2) + ':' + -- ss
    SUBSTRING(@DATESTR, 20, 3) +       -- mmm
    RIGHT(@DATESTR, 2)                 -- am/pm
, 109) AS Converted
-- 2015-03-12 16:07:36.000

Answer 3

这可能会帮助您构建逻辑

SELECT  CAST(CONVERT(VARCHAR(9), LEFT('12-MAR-15 04.07.36.000000000 PM',9), 120) AS DATE) DATE_PART , 
CASE WHEN (RIGHT('12-MAR-15 04.07.36.000000000 PM', 2)='PM' AND Substring('12-MAR-15 04.07.36.000000000 PM',  11, 2) <> '12') 
    THEN Cast(Cast(Substring('12-MAR-15 04.07.36.000000000 PM', 11, 2) AS INT)+12 AS  CHAR(2)) 
 WHEN (RIGHT('12-MAR-15 04.07.36.000000000 PM', 2)='AM' AND Substring('12-MAR-15 04.07.36.000000000 PM', 11, 2)='12') 
    THEN '00'
 ELSE Substring('12-MAR-15 04.07.36.000000000 PM', 11, 2) END + ':' + Substring( '12-MAR-15 04.07.36.000000000 PM',  14, 2) TIME_PART