T-SQL如何将float型时间数值转换为Excel同款12小时制带AM/PM格式

实现方案 #

原理说明 #

Excel的时间存储逻辑为将完整1天（24小时）记为数值1，你表中的TimeField浮点值本质是目标时间占全天的比例，和SQL Server中datetime类型的小数部分规则完全匹配，直接转换后格式化即可得到和Excel一致的效果。

适用SQL Server 2012及以上版本（推荐） #

直接使用FORMAT函数实现12小时制带AM/PM标识的格式化输出：

SELECT 
    LocCd,
    TimeField,
    FORMAT(CAST(TimeField AS DATETIME), 'h:mm:ss tt') AS FormattedTime
FROM tablename;

适用SQL Server 2008及更早版本（无FORMAT函数） #

使用CONVERT函数搭配样式码109截取时间部分：

SELECT 
    LocCd,
    TimeField,
    LTRIM(RIGHT(CONVERT(VARCHAR(30), CAST(TimeField AS DATETIME), 109), 11)) AS FormattedTime
FROM tablename;

异常场景处理 #

如果你的TimeField存在大于1的取值（即包含完整天数的数值），仅需要提取时间部分的话，对TimeField做取模1处理即可：

-- 以FORMAT版本为例，旧版本同理替换CAST部分即可
FORMAT(CAST(TimeField % 1 AS DATETIME), 'h:mm:ss tt')

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