How to remove trailing zeros from milliseconds value(datetime 2)in string format by one query

Question

I used SQL Server. I converted value from datetime2 column with different scales to string and compare them to process null values if they are exist. So, I need to convert this data without tailing nulls by one query without any procedures For example,

'2018-06-23 07:30:20.100' should be '2018-06-23 07:30:20.1'
'2018-06-23 07:30:20.000' should be '2018-06-23 07:30:20.'
'2018-06-23 07:30:20.101' should be '2018-06-23 07:30:20.101'

I used following:

select CONVERT(VARCHAR, col1, 126)  from  [DBO].[DATE_TABLE1]

But it shows unexpected result:

'2018-06-23 07:30:20.100' defined as '2018-06-23 07:30:20.100' - **unexpected(trailing zeros weren't removed)**
'2018-06-23 07:30:20.000' defined as '2018-06-23 07:30:20' - expected    
'2018-06-23 07:30:20.101' defined as '2018-06-23 07:30:20.101' - expected

how can I convert datatime2 value without trailing zeros? Thank you

Answer 1

Thank you for all. On the contrary, I decided to add zeros until max fraction seconds value 7. And I understand that this solution conflicts with my question, but it helps to compare datetime2 values in the string format. I decided to use following statement:

 CONVERT(VARCHAR, CAST({{ columnName }}  AS DATETIME2), 121)

I used This value will get information with all fraction seconds values, therefore I can able to compare values from columns with different scale(fractional seconds) values. If it had been Snowflake, I would 'YYYY-MM-DD HH:MI:SS.FF9'... In sql server, for example, we have 2 table with column that has datetime2 with different values.

create table [DBO].[DATE_TABLE1] (col1 datetime2(1))
create table [DBO].[DATE_TABLE2] (col1 datetime2(7))

I inserted same value into them '2018-06-23 07:30:20.1' After performing 'CONVERT(VARCHAR, CAST({{ columnName }} AS DATETIME2), 121)' for 2 table I will get same string values:

'2018-06-23 07:30:20.10000000' from [DBO].[DATE_TABLE1]
'2018-06-23 07:30:20.10000000' from [DBO].[DATE_TABLE2]

And these values will be equal. If I used 'CONVERT(VARCHAR, {{ columnName }} , 121)', I will get different values:

'2018-06-23 07:30:20.1' from [DBO].[DATE_TABLE1]
'2018-06-23 07:30:20.10000000' from [DBO].[DATE_TABLE2]

Answer 2

Try this:

DECLARE @DataSource TABLE
(
    [value] DATETIME2(3)
);

INSERT INTO @DataSource ([value])
VALUES ('2018-06-23 07:30:20.100')
      ,('2018-06-23 07:30:20.000')
      ,('2018-06-23 07:30:20.101');

SELECT [value]
      ,CONVERT(VARCHAR(20), [value], 121) + REPLACE(FORMAT(DATEPART(MILLISECOND, [value]) / 1000.0, 'g3'), '0.', '') AS [new_value]
FROM @DataSource;