5

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.

  • Looks pretty good as is to me. – Gareth Lyons Feb 17 '17 at 14:13
  • 1
    Well, depending on the activity on the table, I would consider a trigger to quietly remove any time component instead of raising an error when some poor user tried to set it equal to GETDATE(). Also I would encourage you to always spell out the datename components. Did typing dd save you anything, really, over day? This is fine for day but other shorthand will bite you (or readers who learn from you). – Aaron Bertrand Feb 17 '17 at 14:20
  • Thanks @AaronBertrand, I'll change it. If this date is part of PK can I change it using a trigger? – McNets Feb 17 '17 at 14:22
3

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);.

  • Very clear. Let me think about it. This forces me to rewrite actual 'AFERT INSERT, UPDATE' trigger. Thanks again. – McNets Feb 17 '17 at 14:50
  • @McNets oh if you already have insert/update triggers, just add this calculation to those. I neglected to mention that if you don't have the constraint you would need an update trigger too. But you could also do both - an instead of insert trigger to silently allow values to be inserted, and a check constraint to scold updates. :-) – Aaron Bertrand Feb 17 '17 at 14:54

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.