SQL Server CE: how to save an amound of time?

Question

I need to save an amount of time (3 days, 1 month, 5 years, etc).

Datetime seems to be for saving a date and time (4/5/14 21:03). I could save the number of days as a number, but I will like to be able to save in different units (days, weeks, months...)

Is there any way of doing it?

Answer 1

SQL Dynamic DatePart when using DateDiff

So you are probably stuck with Dynamic SQL or using a CASE statement.

Basically, you need to store datepart and number to apply DATEADD (datepart , number , date) to a date. Splitting one string on each value might be too resource-consuming, so it might be best to use two separate fields: char(1) for datepart and smallint (-32,768 to 32,767 should be enough) for number to save on storage space.

Without user functions and Dynamic SQL support in SQL Server Compact the only option to implement the logic seems to be through the CASE statement:

SELECT 
  CASE [datepart]
    WHEN 'y' THEN DATEADD (yy, [number], [date])
    WHEN 'm' THEN DATEADD (mm, [number], [date])
    WHEN 'w' THEN DATEADD (ww, [number], [date])
    ELSE          DATEADD (dd, [number], [date])
  END As [New Date]
FROM ...

Answer 2

Save the amount of time in seconds, store in int or bigint