-1

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

Эльфия Валиева
  • 127
  • 2
  • 9
  • 3
    In SQL, you don't. It's a task for your presentation layer. – Thom A Nov 30 '20 at 08:39
  • 1
    `I converted value from datetime2 column with different scales to string and compare them to process null values if they are exist` why???? `datetime2` has no trailing zeros, it's a binary value just like `decimal`. You don't need to convert it to string to check for null, in fact that's pointless - a null will still be a null. Is the *real* question how to check for equality? You wouldn't convert a `decimal` to a string to check for equality. You'd either convert both values to the same precision or check whether the absolute difference is less than a limit – Panagiotis Kanavos Nov 30 '20 at 08:53
  • 2
    So what do you actually want to do? Whatever it is, strings aren't involved – Panagiotis Kanavos Nov 30 '20 at 08:53
  • 1
    `should be '2018-06-23 07:30:20.1'` no it shouldn't. Humans don't write milliseconds like this except in old digital watches. That `.1` is 100 ms and nobody expects to see this as `.1`, unless there's no space to display more digits. Even then, the number of digits is fixed - in a stopwatch you'd *always* see only one fractional digit – Panagiotis Kanavos Nov 30 '20 at 08:58
  • @PanagiotisKanavos, it is not important why. But I compare values from 2 tables, and this verification is needed. The same value will be represented differently in the columns datetime2(1) and datetime2(3) for example. '2018-06-23 07:30:20.1' and '2018-06-23 07:30:20.100' don't equal to each other in the string format, although I inserted same value '2018-06-23 07:30:20.1' – Эльфия Валиева Nov 30 '20 at 09:07
  • 2
    It *is* very important, because the question doesn't make sense as-is. There are no trailing zeros - the two values **are identical**. If you wrote `where t1=t2` you'd get a match. Precisely because there's no format. Have you tried comparing them the way they are? If you need to truncate milliseconds, use `cast(colN as datetime2(1))` to convert to the same precision – Panagiotis Kanavos Nov 30 '20 at 09:14
  • 1
    *"it is not important why"* it is important, as like I said this isn't something to do in SQL; you can't have a column with varying precisions. This, to both myself and @PanagiotisKanavos , appears to be an [XY Problem](http://xyproblem.info) and why we need to know what you are *really* trying to do. – Thom A Nov 30 '20 at 09:16
  • *"'2018-06-23 07:30:20.1' and '2018-06-23 07:30:20.100' don't equal to each other in the string format"* Then stop using strings... But you state that the data type, in your title, is a **`datetime2`**, and a `datetime2` is **not** a string. '2018-06-23 07:30:20.1' and '2018-06-23 07:30:20.100' **do** equal each other. [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019l&fiddle=228a852bea21ba62dd83017dc273ea63) – Thom A Nov 30 '20 at 09:26
  • @Larnu see my upper comment – Эльфия Валиева Nov 30 '20 at 10:06
  • Which? None state what your true goal is here. You only tell us that *""'2018-06-23 07:30:20.1' and '2018-06-23 07:30:20.100' don't equal to each other"* which is fundamentally not true with date and time data types, such as a `datetime2`; as I have evidenced. Don't use strings for date and time values... [`varchar` is not a one size fits all datatype](https://wp.larnu.uk/fundamentals-varchar-is-not-a-one-size-fits-all-data-type/). – Thom A Nov 30 '20 at 10:09
  • With respect, ЭльфияВалиева , @PanagiotisKanavos hasn't been aggressive at all, they are trying to understand the real problem you have (that you are using strings for dates). Then only person being aggressive there is you by SHOUTing at them; I suspect you have now lots their interest now when they were actually trying to help you, for free, in their own free time. You have certainly lost mine. – Thom A Nov 30 '20 at 10:11
  • @PanagiotisKanavos think what you want. But I would answer the question asked. And thank you for trying to help, but unfortunately you did not understand the essence of the question. – Эльфия Валиева Nov 30 '20 at 10:17
  • @Larnu I compare date values in string format, because null is not equal to null!create table alf.simpletbl (col1 int, col2 int) create table alf.simpletbl2 (col1 int, col2 int) insert into alf.simpletbl2 values(1,1); insert into alf.simpletbl2 values(null, null); insert into alf.simpletbl1 values(1,1); insert into alf.simpletbl1 values(null, null); select a.* from alf.simpletbl a, alf.simpletbl2 b where a.col1 = b.col1 And I got only 1 row, not 2!! – Эльфия Валиева Dec 02 '20 at 10:59
  • *Nothing* is equal to `NULL`, @ЭльфияВалиева , including `NULL`... If you need to check `NULL` values use `IS NULL`. Using a `varchar` instead of a date and time datatype because you're incorrectly handling `NULL` values just makes the problem worse... – Thom A Dec 02 '20 at 11:01
  • @PanagiotisKanavos see upper comment, that I sent to Larnu, null value doesn't equal to null! And I compare 2 tables. – Эльфия Валиева Dec 02 '20 at 11:02
  • @Larnu I avoid to use is null in my sql statements! I use join operators too, my sql statement is difficult. – Эльфия Валиева Dec 02 '20 at 11:03
  • Then do `ON ((Col1 IS NULL AND Col2 IS NULL) OR Col1 = Col2)` I cannot reiterate more that `varchar` is *not* a one size fits all data type... Using a `varchar` to store a date is a design fault, no questions asked. – Thom A Dec 02 '20 at 11:07
  • @ЭльфияВалиева because comparing NULL with anything still returns NULL so the comparison fails. That's how SQL, the language, works. In all databases you have to use `IS NULL` or `IS NOT NULL` to check whether a value is null or not. This has *nothing8 to do with types. And no, converting to `varchar` didn't solve anything. It *introduced* additional problems, like preventing the server from using indexes – Panagiotis Kanavos Dec 02 '20 at 11:11
  • @PanagiotisKanavos I cannot change tables, I use customer tables, I cannot change them by adding index or so on. This query is used for only tables without primary key. Also you don't know whole picture – Эльфия Валиева Dec 02 '20 at 11:48

2 Answers2

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;

enter image description here

gotqn
  • 42,737
  • 46
  • 157
  • 243
  • 1
    This isn't necessary. The two columns can be compared directly because `.1` and `.100` are the same value. If one of the fields had extra *significant* digits that had to be truncated, `cast( ... as datetime2(1))` would convert them to the same precision – Panagiotis Kanavos Nov 30 '20 at 09:15
  • @PanagiotisKanavos but you will lose `101` if you cast to `datetime2(1)` or I am missing something? – gotqn Nov 30 '20 at 10:06
  • @gotqn the OP is trying do compare `datetime2` values of different precision. This has nothing to do with formatting. It's an XY question at the very least. BUT the OP removed this part from the question to avoid explaining why the values aren't compared directly – Panagiotis Kanavos Nov 30 '20 at 10:40
-2

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]
Эльфия Валиева
  • 127
  • 2
  • 9