We store datapoints as a double.  Over the last 3 years, this has fit every situation we needed for the User_Group_Datapoint_Timeframe persistent storage.  Our latest requirement is to store a financial date of submission each month.    The challenge was taking the date time and converting it to a number.

These are the two transforms we use to convert the date to a number an back in code:

   1:  

   2: //Transform from a date to a number

   3: public object Transform(object objectToTransform)

   4: {

   5:     if (!Utils.IsDate(objectToTransform))

   6:         return null;

   7:  

   8:     DateTime dt = System.DateTime.Parse(objectToTransform.ToString());

   9:     return Double.Parse(dt.Ticks.ToString());

  10:  

  11: }

   1: //Transfer an int64 to date in the format of MM/dd/yyyy

   2: public object Transform(object objectToTransform)

   3: {

   4:     if (objectToTransform == null) 

   5:         return string.Empty;

   6:     double d = double.Parse(objectToTransform.ToString());

   7:     if (d <= 0) return string.Empty;

   8:     long l = Convert.ToInt64(d);

   9:     DateTime dt = new DateTime(l);

  10:     return dt.ToString("MM/dd/yyyy");

  11: }

I also had to store some values directly in sql, so I found this handy article on code project:

Convert DateTime To .NET Ticks Using T-SQL

We've only had to worry about the date being accurate up to the day thus far.  I'm interested in doing some testing down the road on the conversions to the millisecond.

A Sql helper function:

CREATE FUNCTION [dbo].[DateTimeToTicks] (@d datetime)
RETURNS bigint
WITH SCHEMABINDING
AS
-- converts the given datetime to .NET-compatible ticks
-- see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclasstickstopic.asp
BEGIN
RETURN
dbo.DateToTicks(DATEPART(yyyy, @d), DATEPART(mm, @d), DATEPART(dd, @d)) +
dbo.TimeToTicks(DATEPART(hh, @d), DATEPART(mi, @d), DATEPART(ss, @d)) +
(CONVERT(bigint, DATEPART(ms, @d)) * CONVERT(bigint,10000));
END

GO