Stream Analytics查询中字符串转DateTime失败，请求解决方案

你的Stream Analytics作业失败，核心原因是发送的DateTime字符串格式不符合标准规则——你提供的2018-03-20 10:50:037.996里，秒的部分是037，这既超出了秒00-59的合法取值范围，也不符合datetime格式中秒为两位数字的要求，导致CAST函数无法完成转换。

下面给你两种针对性的解决方案，你可以根据实际场景选择：

方案1：从数据源修正格式（最优选择） #

如果你能控制数据发送的源头，直接把DateTime字符串调整为标准格式即可——将10:50:037.996修改为10:50:37.996（去掉秒位前多余的0，确保秒是两位有效数字）。修改完成后，你原本的查询语句就能正常执行，不需要做任何调整。

方案2：在查询中修复格式后再转换 #

如果无法修改数据源，那就在Stream Analytics的查询里先修正非法的字符串，再进行datetime转换。这里提供两种实现方式：

写法一：精准截取拼接（适配固定格式错误） #

SELECT 
    powerscout.Device_Id AS PowerScout, 
    powerscout.[kW System], 
    CAST(
        CONCAT(
            LEFT(powerscout.[TimeStamp], 14), -- 提取到分钟部分：2018-03-20 10:50:
            RIGHT(LEFT(powerscout.[TimeStamp], 17), 2), -- 提取秒的有效两位：37
            SUBSTRING(powerscout.[TimeStamp], 18, 4) -- 提取毫秒部分：.996
        ) AS datetime
    ) AS [TimeStamp]
INTO [PowergridView]
FROM [IoTHubIn]

这个写法的思路是将错误字符串拆分后，去掉秒位前的无效字符，重新拼接成合法的datetime格式，再完成转换。

写法二：简单替换（仅适用于秒位带前导0的情况） #

如果你的错误都是秒位多了一个前导0（比如037这种固定模式），可以用REPLACE快速处理：

SELECT 
    powerscout.Device_Id AS PowerScout, 
    powerscout.[kW System], 
    CAST(REPLACE(powerscout.[TimeStamp], ':0', ':') AS datetime) AS [TimeStamp]
INTO [PowergridView]
FROM [IoTHubIn]

注意：这种方法有局限性，如果出现秒数超过59的非法值（比如61），依然会转换失败，因此更推荐第一种精准处理的写法。

内容的提问来源于stack exchange，提问作者krishna bh