Retrofitting date values in SQL Server 2005

from the Artful SQL Server & Access Tips List

In a time-tracking database that I worked on recently, the table of interest contained these columns:

  [BillingDate] [smalldatetime] NULL ,
  [WorkDate]    [smalldatetime] NULL ,
  [Start]       [datetime] NULL ,
  [Stop]        [datetime] NULL,
  [Minutes]     [smallint] NULL,

You can make several criticisms of this table design. Most obvious is the use of NULLable columns—this data makes no sense if any of the values are null. (There may be a case for allowing nulls in the Stop column; for instance, if the data-entry person could record the start time, then work on the task, and then record the stop time upon completion.)

Also, the columns Start and Stop use the datetime column type, which is wasteful since the column's data of interest is the time portion of the value, not the date. (The front-end application uses an input mask to show only the time portion of each value.) As a result, SQL defaults the date portion of newly entered values to 30/12/1899. This prevented the data type from changing to smalldatetime, since that date falls outside the range of values permitted in a smalldatetime column.

The smallest permitted smalldatetime value is 1/1/1900, so you could get around this by simply adding two days to each existing value. However, that value is nonsensical and of no interest to the application in question. On the other hand, the column WorkDate ignores the time portion of its values, so they all default to 00:00.

How I retrofitted the date values in this table

I chose to change the Start and Stop columns so the column's date portion was the same date as contained in the WorkDate column. Then, their values would be accurate and meaningful.

I also decided to make one more change to the table. The Minutes column was a physical column and required a calculation, either in the database itself or in the front-end application. I used the following code to change it to a virtual column:

[Minutes] [smallint] NULL AS (datediff(minute,[start],[stop]))

It took a few minutes of experimentation to derive the statement that would update the Start and Stop columns to use the WorkDate value as their date portion, while preserving their existing time portion. Here is the statement I used:

UPDATE timetrackerdetails
SET start = DATEADD(dd, DATEDIFF(dd, start, workdate), start),
    stop = DATEADD(dd, DATEDIFF(dd, stop, workdate), stop)

To see how this works, read it inside out. The DATEDIFF() function returns the number of units between two date values (in this case, the dd argument is specified, so the function returns the number of days between the values). This number of days is then added to the original values of Start and Stop. As a result, the Start and Stop columns now reflect the WorkDate, while preserving their time portions.
Return to the Artful SQL Server & Access tips page