Reputation: 1
How can I use any cast or convert function to remove trailing zeros from milliseconds in datetime2 data type.
For example, I have these data:
2018-02-17 13:26:55.033000
2018-02-17 12:37:12.300000
2018-02-17 14:55:30.110000
I want to change it to:
2018-02-17 13:26:55.033
2018-02-17 12:37:12.3
2018-02-17 14:55:30.11
Thanks in advance.
Upvotes: 0
Views: 1390
Reputation: 2021
Get the millisecond part, reverse it, convert it to INT and again Reverse. Please try-.
;WITH CTE AS
(
SELECT
GETDATE() CompleteDate,
CAST ( GETDATE() AS DATE ) DtPart
,CONVERT(VARCHAR, GETDATE () , 24 ) TmPart
,REVERSE(CAST(REVERSE(DATEPART(MILLISECOND,GETDATE())) AS INT)) MilliPart
)
SELECT CompleteDate
, CONCAT( DtPart, ' ' , TmPart , '.' , MilliPart ) [DateTimeWithOutZero]
FROM CTE
CompleteDate DateTimeWithOutZero
----------------------- ------------------------------------------------------------------------------------
2018-07-18 14:22:52.190 2018-07-18 14:22:52.19
(1 row affected)
Upvotes: 0
Reputation: 4334
-- 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
Upvotes: 1
Reputation: 1156
Try this. AS Squirrel mentioned this will return nvarchar and not datetime.
CREATE TABLE #temp
(
YourColumn DATETIME2
);
INSERT INTO #temp ( YourColumn )
VALUES ( GETDATE() -- YourColumn - datetime2
);
SELECT *
FROM #temp;
SELECT REVERSE(
SUBSTRING(
REVERSE(CONVERT(VARCHAR(100), YourColumn, 121)) ,
PATINDEX(
'%[1-9]%' , REVERSE(CONVERT(VARCHAR(100), YourColumn, 121))),
LEN(YourColumn)
- PATINDEX(
'%[1-9]%' ,
REVERSE(CONVERT(VARCHAR(100), YourColumn, 121))) + 1)) AS YourColumnTrim
FROM #temp;
DROP TABLE #temp;
Result:
+-----------------------------+
| YourColumn |
+-----------------------------+
| 2018-07-18 13:22:54.1530000 |
+-----------------------------+
+-------------------------+
| YourColumnTrim |
+-------------------------+
| 2018-07-18 13:22:54.153 |
+-------------------------+
Upvotes: 0