Home Forums SQL Server 2005 T-SQL (SS2K5) Update Datetime Column to Empty String - No Error - Strange Results RE: Update Datetime Column to Empty String - No Error - Strange Results

  • You might be right. Here is what I pulled from the HELP from SSMS for "Datetime data type >> about datetime data type":

    " ... Values with the datetime data type are stored internally by the Microsoft SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. ... "

    Edit:

    For me this has been confusing for a couple of decades and I still need help understanding this:

    select

    convert(binary(8), cast(0 as datetime)) as [zero date],

    convert(binary(8), cast('1/1/1900 00:00:00.003' as datetime)) as [smallest tick],

    convert(binary(8), cast(1 as datetime)) as [zero date plus one],

    convert(int, substring(convert(binary(8), cast(1 as datetime)), 1, 4)) as [day integer]

    -- zero date smallest tick zero date plus one day integer

    -- ------------------ ------------------ ------------------ -----------

    -- 0x0000000000000000 0x0000000000000001 0x0000000100000000 1

    select

    cast('1900-01-01 23:59:59.999' as datetime) as [zero date plus one],

    convert(binary(8), cast('1900-01-01 23:59:59.999' as datetime))

    as [zero date plus one],

    convert(binary(8), cast('1900-01-02 00:00:00.007' as datetime))

    as [zero date + 1 + 2 ticks],

    cast(substring(convert(binary(8),

    cast('1900-01-02 00:00:00.007' as datetime)), 5, 4) as integer)

    as [time integer]

    -- zero date plus one zero date plus one zero date + 1 + 2 ticks time integer

    -- ----------------------- ------------------ ----------------------- ------------

    -- 1900-01-02 00:00:00.000 0x0000000100000000 0x0000000100000002 2

    select

    convert(binary(8), cast('1/1/1900 00:00:01.000' as datetime)) as [one second],

    convert(int, 0x012c) as [ticks per second]

    -- one second ticks per second

    -- ------------------ ----------------

    -- 0x000000000000012C 300

    select

    cast(1 as datetime) - cast('00:00.003' as datetime) as [End of first Day],

    convert(binary(8), cast(1 as datetime) - cast('00:00.003' as datetime))

    as [End of First Day],

    cast(substring(convert(binary(8),

    cast(1 as datetime) - cast('00:00.003' as datetime)), 5, 4) as integer)

    as [End of First Day],

    300 * 60 * 60 * 24 as [ticks per day]

    -- End of first Day End of First Day End of First Day ticks per day

    -- ----------------------- ------------------ ---------------- -------------

    -- 1900-01-01 23:59:59.997 0x00000000018B81FF 25919999 25920000