SQL Server 2005 - Best way to constraint a Datetime field as Date

Whereas:


date field type starting with SQL Server 2008

Given a table:

CREATE TABLE dbo.MyTable
(
    Code int NOT NULL,
    DateCode datetime NOT NULL
)
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
[Code] ASC, [DateCode] DESC
);

Which is the best way to CONSTRAINT that DateCode is only a Date?

It must allow dates without time part.

2017-01-02
2017-01-02 00:00:00.000
20170102

By now I'm using:

ALTER TABLE dbo.MyTable ADD CONSTRAINT [CHK_MyTable_DateCode]
    CHECK ([DateCode] = DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateCode])));

But, I wonder if there is a best/short way to accomplish it.

You can simplify it a little bit, since SQL Server knows with datetime that it can treat the value as the number of days since 0. I prefer to use '19000101' over zero, since it's slightly less cryptic what's going on:

ALTER TABLE dbo.MyTable
ADD CONSTRAINT CHK_MyTable_DateCode
CHECK (DateCode = DATEDIFF(DAY, '19000101', DateCode));

However, I find it an odd choice to scold users who might use built-ins like GETDATE() or parameters like @OrderDate which they don't realize contains a time component. So a slightly cleaner way to solve this would be with a trigger:

CREATE TRIGGER dbo.MyTable_RoundCodeDate
ON dbo.MyTable
INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;
  INSERT dbo.MyTable(Code, DateCode)
    SELECT Code, DATEDIFF(DAY, '19000101', DateCode)
    FROM inserted;
END
GO

Using an INSTEAD OF trigger is not popular with a few people here, but I think it is better to massage those values before inserting than after. PK will be handled the same as without a trigger - violations will still be caught at insert time (with an after trigger, though, you wouldn't hit the violation until after the trigger code runs, which theoretically at least means more logging). In fact you could add a GROUP BY to the trigger to silently ignore duplicates from the source statement, e.g. INSERT ... VALUES(1,GETDATE()), (1,CURRENT_TIMESTAMP);.

StackExchange Database Administrators Q#164678, answer score: 3