Implementing (Date - Time Delta) in SQL Server 2008

Question

I want to be able to send reminders for appointments. Given the tables:

- Appointment
  ID (PK)
  Start

- Reminder
  AppointmentID (FK)
  MinutesBeforeAppointmentToSendReminder -- only need minute resolution

I would like to select reminder times:

select ..., DateAdd(minutes, -Reminder.MinutesBeforeAppointmentToSendReminder, Appointment.Start) as ReminderTime
from Appointment join Reminder
  on (Appointment.ID = Reminder.AppointmentID)
where (...)

The database platform is SQL Server 2008. LinqToSql will be used to access the database.

There are a slew of date/time types and functions in SQL Server 2008. What are the best types to use for Start and MinutesBeforeAppointmentToSendReminder. What is the best date function to use? [i.e., "best" considering performance, convenience and portability]

(Was planning on DateTime, int, DateAdd)

Answer 1

Use dateadd.

Its quicker and nicer to read than converting the numbers between datetimes and floats and doing the maths on the raw date values.

Your application questions - how far in advance are you sending your reminders? You might use ints or tinyints if 4 hours is your limit. Standard DateTime is likely enough for your start date; unless you need timezone support in which case datetimeoffset might be more appropriate.

Answer 2

If you need to resolve to the minute only, then use smalldatetime not datetime.

For differences, I'd suggest smallint which gives you 32k minutes and DATEADD which keep calculation in the date/stime-type domain