5.3 Enforcing Granularity Rules

5.3.1 Problem

As a designer of a database, you don't trust your user-interface programmers, and you want to explicitly enforce the granularity of one day in your database. Bottom line: you are using the DATETIME to store dates, and you want to prevent programmers from mistakenly storing time-of-day values in your DATETIME fields.

5.3.2 Solution

Use a trigger to intercept all inserts and updates and have that trigger remove any inadvertent time-of-day values:

CREATE TRIGGER ContractorSchedulesUpdate
ON ContractorsSchedules
FOR UPDATE, INSERT
AS 
    UPDATE ContractorsSchedules 
    SET JobStart=CONVERT(CHAR(10),i.JobStart,120), 
      JobEnd=CONVERT(CHAR(10),i.JobEnd,120)
    FROM ContractorsSchedules c, inserted i 
    WHERE c.JobId=i.JobId

With this trigger in place, programmers are restricted to storing only date values in the JobStart and JobEnd columns. They can try to insert a date and a time, but the time will be ignored. For example, consider the following INSERT statement:

INSERT INTO ContractorsSchedules(JobID, ContractorID, JobStart, 
JobEnd, JobType) 
VALUES('', 'Cindy', '2001-1-1 05:12','2001-1-10 19:15', 'H')

Even though this INSERT statement specifies both a date and a time for JobStart and JobEnd, only the dates were accepted:

SELECT ContractorId, JobStart, JobEnd 
FROM ContractorsSchedules WHERE ContractorId='Cindy'

ContractorId JobStart                   JobEnd                                                 
------------ ----------------------- ----------------------- 
Cindy        2001-01-01 00:00:00.000 2001-01-10 00:00:00.000

As you can see, the server cut off the time information that was mistakenly included in the INSERT statement.

If you're following along with our examples, please delete the previously inserted row after you finish studying this recipe. Use the command: DELETE FROM ContractorsSchedules WHERE ContractorId='Cindy'.

5.3.3 Discussion

The trigger brings additional overhead to INSERT and UPDATE operations; however, it provides you with the security of knowing for sure that all temporal information is stored with the required granularity. You control the granularity by adjusting the constant in the CONVERT clause. For example, if you want to set the granularity to hours, you would extend the constant by three more characters:

CREATE TRIGGER ContractorSchedulesUpdate
ON ContractorsSchedules
FOR UPDATE, INSERT
AS 
    UPDATE ContractorsSchedules 
    SET JobStart=CONVERT(CHAR(13),i.JobStart,121)+':00', 
      JobEnd=CONVERT(CHAR(13),i.JobEnd,121)+':00'
    FROM ContractorsSchedules c, inserted i 
    WHERE c.JobId=i.JobId

With this trigger in place and using the same insert as shown in the recipe, the results will be as follows:

ContractorId JobStart                   JobEnd                                                 
------------ ----------------------- ----------------------- 
Cindy        2001-01-01 05:00:00.000 2001-01-10 19:00:00.000

Setting the scope of the CHAR type effectively chops off the unwanted characters from the ISO format YYYY-MM-DD HH:MI:SS.MMM, so that we are left with YYYY-MM-DD HH. However, after that we are violating the required ISO format, so we add the string ':00' to comply with the required form, which requires at least the minutes along with the hour. In this way, you can easily restrict the granularity in a table to any degree you desire.

Please note that this solution does not prevent you from using the wrong temporal granularity in queries; i.e., in SELECT statements, it just prevents you from storing information with a granularity finer than you require.