sql - SQL Server - 如何从 datetime2 数据类型的毫秒中删除尾随零

Question

如何使用任何强制转换或转换函数从 datetime2 数据类型的毫秒中删除尾随零。

例如，我有这些数据：

2018-02-17 13:26:55.033000
2018-02-17 12:37:12.300000
2018-02-17 14:55:30.110000

我想将其更改为：

2018-02-17 13:26:55.033
2018-02-17 12:37:12.3
2018-02-17 14:55:30.11

提前致谢。

标签： sqlsql-serverdatetime2

Answer 1

-- Sample DDL/DML statements:
declare @tbl table (dt datetime2)
insert into @tbl values 
('2018-02-17 13:26:55.033000'),
('2018-02-17 12:37:12.300000'),
('2018-02-17 14:55:30.110000')

-- Use this recursive CTE to trim trailing zeros.
-- The dt col is the original value, the rn col is a 
-- recursive incrementor, and the vc col is the varchar
-- representation of the datetime2.
;with cte as 
(
    select dt, 1 as rn, cast(dt as varchar(100)) vc 
    from @tbl
    union all 
    select dt, rn + 1, SUBSTRING(vc, 1, len(vc) - 1)
    from cte 
    where SUBSTRING(reverse(vc), 1, 1) = '0'
)
select vc 
from cte 
where rn = (select max(rn) from cte c where dt = cte.dt)

vc
2018-02-17 14:55:30.11
2018-02-17 12:37:12.3
2018-02-17 13:26:55.033