• My apologies. I normally document what I did a lot better but I was in a rush. Here's how I came to those numbers from the excellent description of the problem you gave. As usual with me, the details are in the comments in the code below...

    /*************************************************************************************************

    To give some details.

    1. Quartz.Net stores its time values in a bigint.

    2. The value is the column is the number of ticks that have elapsed since January 1, 0001 12:00am.

    3. All ticks values are UTC date time values

    4. There are 10000 ticks in a millisecond.

    5. For example November 24, 2010 16:30 would be 634262130000000000

    *************************************************************************************************/

    --===== Here's the first number

    SELECT MillisecondsPerDayTimes10000 = DATEDIFF(ms,'19000101','19000102')*10000.0

    --===== If we divide the number of ticks for November 24, 2010 16:30

    -- by the number of ticks per day (the first number), we end up

    -- with a "Date Serial Number" where the whole numbers represent

    -- the number of days since the January 1, 0001 12:00am epoch. The

    -- decimal places represent the partial day which is also known

    -- as the "Decimal Time".

    SELECT Epoch1DateSerial = 634262130000000000/(DATEDIFF(ms,'19000101','19000102')*10000.0)

    --===== Although SQL Server's DATETIME datatype is represented by two

    -- 4 byte integers, they are most correctly interpreted using a

    -- float conversion thusly...

    SELECT Epoch2DateSerial = CAST(CAST('November 24, 2010 16:30' AS DATETIME) AS FLOAT)

    --===== Since both Date Serial Numbers are for the same date and time,

    -- (November 24, 2010 16:30), simple subtraction gives us the second

    -- number which is the correction factor to convert one epoch to

    -- the other as a number of days.

    SELECT CorrectionFactorDays =

    634262130000000000/(DATEDIFF(ms,'19000101','19000102')*10000.0) --Epoch1DateSerial

    - CAST(CAST('November 24, 2010 16:30' AS DATETIME) AS FLOAT) --Epoch2DateSerial

    --===== Finally, we make the simple substitutions to come up with the

    -- simple formula to make the conversion to produce the correct

    -- Date Serial Number from the number of ticks and convert that

    -- Date Serial Number to an SQL Server DATETIME datatype and

    -- we're done.

    DECLARE @Ticks BIGINT

    SELECT @Ticks = 634262130000000000

    SELECT CAST(@Ticks/864000000000.0 - 693595 AS DATETIME)

    For those that don't know what an "Epoch" is...

    http://www.merriam-webster.com/dictionary/epoch

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)