CODEWITHSUNDEEP
sqlsql-serverdatetime2
Katika Panichapong
Katika Panichapong

Reputation: 1

SQL Server - How to remove trailing zeros from milliseconds in datetime2 data type

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

Answers (3)

Pawan Kumar
Pawan Kumar

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

Max Szczurek
Max Szczurek

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

AB_87
AB_87

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

Related Questions