LEFT JOIN中LogonTime列DATE类型转换失败问题求助

问题描述 #

使用CONVERT、CAST、TRY_CONVERT函数无法将LogonTime列转换为DATE类型，转换为VARCHAR时结果显示为月份名称格式（如NOV 17 2022 10:56 AM）。该列示例值为11/17/2022 10:56:38 PM，期望得到11/17/2022或11-17-2022格式的日期字符串。

原查询语句：

SELECT Subquery.*, rl.LogonTime as Logon 
FROM (SELECT su.Name, sg.Name as Security 
    FROM SecUser su
    LEFT JOIN SecGroupChild sgc ON sgc.ChildKey = su.UniqueID
    LEFT JOIN SecGroup sg ON sgc.GroupKey = sg.UniqueID
) AS Subquery
LEFT JOIN (
    SELECT Username, LogonTime
    FROM RankedLogins
    WHERE RowNum = 1
) 
rl ON rl.Username = Subquery.Name

解决方案 #

核心原因 #

大概率是LogonTime列实际存储类型为字符串（而非datetime类型），直接转换时因格式不匹配导致失败；未指定转换样式时，SQL Server会使用默认格式输出月份名称。

修改后的查询语句 #

根据期望格式选择以下任一方式修改子查询中的LogonTime处理逻辑：

方式1：输出11/17/2022格式

SELECT Subquery.*, rl.FormattedLogonTime as Logon 
FROM (SELECT su.Name, sg.Name as Security 
    FROM SecUser su
    LEFT JOIN SecGroupChild sgc ON sgc.ChildKey = su.UniqueID
    LEFT JOIN SecGroup sg ON sgc.GroupKey = sg.UniqueID
) AS Subquery
LEFT JOIN (
    SELECT Username, 
           -- 先将字符串按mm/dd/yyyy HH:MI:SS PM格式转为datetime，再转为指定格式的字符串
           CONVERT(varchar, TRY_CONVERT(datetime, LogonTime, 101), 101) AS FormattedLogonTime
    FROM RankedLogins
    WHERE RowNum = 1
) 
rl ON rl.Username = Subquery.Name

方式2：输出11-17-2022格式

SELECT Subquery.*, rl.FormattedLogonTime as Logon 
FROM (SELECT su.Name, sg.Name as Security 
    FROM SecUser su
    LEFT JOIN SecGroupChild sgc ON sgc.ChildKey = su.UniqueID
    LEFT JOIN SecGroup sg ON sgc.GroupKey = sg.UniqueID
) AS Subquery
LEFT JOIN (
    SELECT Username, 
           -- 先转datetime，再用样式110输出mm-dd-yyyy格式
           CONVERT(varchar, TRY_CONVERT(datetime, LogonTime, 101), 110) AS FormattedLogonTime
    FROM RankedLogins
    WHERE RowNum = 1
) 
rl ON rl.Username = Subquery.Name

关键参数说明 #

• TRY_CONVERT(datetime, LogonTime, 101)：样式101对应mm/dd/yyyy格式，确保字符串能正确转为datetime类型；
• CONVERT(varchar, ..., 101)：将datetime类型转为mm/dd/yyyy格式的字符串；
• CONVERT(varchar, ..., 110)：将datetime类型转为mm-dd-yyyy格式的字符串。

如果LogonTime本身是datetime类型，可简化为直接使用CONVERT(varchar, LogonTime, 101)或CONVERT(varchar, LogonTime, 110)。

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