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))
  • 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

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


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


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

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


Which outputs (for me, right now):

Oct 30 2018  2:02PM

You can try something more like this:

         '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.

  • @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