Ot: SQL checksum

dev
1

Sorry for the OT but I wonder if anyone has played with MS SQL Server’s checksum function, I’m having an issue with a column of Datetime or Date data type, see below.

– DDL

create table Customer
(
   CustomerID int not null primary key,
   CustomerName varchar(80) not null,
   CustomerPhone varchar(80) not null,
   SalesLeadID int not null,
   FieldOfDate date not null,
   CustomerCheckSum int not null,
constraint CustomerCheckSum
   check (checksum(CustomerID,FieldOfDate) = CustomerCheckSum)
);

– DML, it generated illogical complaint, failed to insert

insert into Customer
values (31, 'John Doe', '555-555-5555', 15,'2017-12-14',CHECKSUM(31,'2017-12-14'));

– TEST, it works

select CHECKSUM(31,'2017-12-14')

Thanks.

2

It seems to me that the one CHECKSUM is called on a string here, before it is converted to DATE, while the other is called on a Date.

Try to use the CONVERT() function to CAST both explicitely, e.g.

CHECKSUM( CONVERT( CHAR(10), CAST(GETDATE() AS DATE), 120 ) )
3

No, it didn’t work, I tried it before; also, the value of ‘2017-12-14’ for a date happens to be {today}, so, CAST the value to DATE would be more appropriate. Thanks tho.

4

That’s not the point. The point is that if one value is calculated on a Date object and the other is calculated on a String, then they will not be equal.

5

Yeah, good point. So, now, how do we know what data type does each of the following use?
checksum(CustomerID,FieldOfDate) during the table creation process?