2

What is best way to Concat on datetime column with other fields, without losing/truncate millisecond precision?

Example: We have a column in datetime precision.

1988-02-27 00:00:04.000

We are creating rowhash on CONCAT

select hashbytes('SHA2_256',concat(SalesRecord,SalesDatetime))

The problem when doing Concat, we are losing second values. You can test here,

create table dbo.SalesOrder
(
    SalesOrderId int primary key, 
    SalesRecord varchar(255), 
    SalesDatetime datetime
)
select concat(SalesRecord,SalesDateTime) from dbo.SalesOrder
select concat('ABC ',[SalesDateTime]) from dbo.SalesOrder

ABC Feb 27 1988 12:00AM

What is the optimal way to solve this?

Background: Rowhash was causing duplicates by removing seconds, pipe deliminator | not working either.

The following was not working:

declare @testdate datetime = '1988-02-27 00:00:04.000'
select concat('ABC',convert(binary(16),@testdate)) 
select concat('ABC',convert(varchar(25),@testdate))
1
  • great answers below, anyone know more optimal one; may not make difference between few rows, we will be conducting for millions, just curious, I am thinking its datetime2(3) – user162241 Oct 30 '18 at 20:29
1

Try converting the DATETIME value to a DATETIME2(3) before concatenating.

DECLARE @now DATETIME = GETDATE();

SELECT 
    CONCAT('ABC ', @now),
    CONCAT('ABC ', CONVERT(DATETIME2(3), @now));

Results:

ABC Oct 30 2018  3:01PM                      ABC 2018-10-30 15:01:30.617
2

You are losing seconds because the default format for converting datetime to string is the same as:

SELECT CONVERT(char(19), GETDATE(), 0);

Which outputs (for me, right now):

Oct 30 2018  2:02PM

You can try something more like this:

SELECT CONCAT
       (
         'ABC ', 
         CONVERT(char(19), GETDATE(), 25)
       );

Which outputs (again, for me, right now):

ABC 2018-10-30 14:02:55

If second aren't enough, just change char(19) to char(23). If that's still not enough, you can consider changing that column to datetime2 to get more precision.

2
  • @knightbob472 You have 10 datetime columns and this (along with other columns, like a unique primary key column) wasn't enough distinctness for row hashing? Do all 10 columns represent the same thing? – Aaron Bertrand Oct 30 '18 at 18:07
  • Well adding .000 to both values won't really change the collision, need to fix the source so that granularity is actually there, but love to hear how you get on. – Aaron Bertrand Oct 30 '18 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy