In a time-tracking database that I worked on recently, the
table of interest contained the columns displayed in
Listing A. 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.

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!