Update Datetime Column to Empty String - No Error - Strange Results

  • Can anyone explain why SQL Server allows a datetime column to be updated with an empty string '' without generating an error? Also doing so sets the datetime to 1/1/1900, why? Here is a little test script to demo:

    use TempDB

    go

    Create table testDt

    (

    id int not null,

    Datetest datetime null

    )

    go

    Insert testDt (id, datetest) values (1, getdate())

    go

    Select * from testDt

    -- Result

    -- idDatetest

    -- 12008-03-21 15:55:00.710

    Update testDt

    set datetest = ''

    where id = 1

    go

    -- 1 row affected

    Select * from testDt

    go

    -- Results

    -- idDatetest

    -- 11900-01-01 00:00:00.000

    Drop table testDt

    go

    Thanks

    CG

  • '' will auto convert to numeric 0 which will autoconvert to the "zero date" which is 1/1/1900. I am pretty sure that it was this way back in the 80s with Sybase and I think this might be Sybase legacy. That is far enough back in the past that I am a bit foggy on my memory of that. In any case the expected behavior is that both an empty and a blank string are autoconverted to 1/1/1900.

  • Wow, I would never thought SQL Server still has legacy code that goes that far back. Thanks for the explanation. However, I would think SQL Server should still throw an error since an empty string is not a valid date.

    CG

  • To further expand what Kent was saying. SQL server has explicit and implicit comversions. Explict conversions when you cast or convert a value to another data type. Implicit conversions occur when you do not specify a data type and SQL will automatically convert the value for you to the column data type.

    In your case the string '' is implicitly converted to a 0. The datatime data type in SQL is not stored as you see it when you query the table. A datetime data type is stored as a float value. The float value starts at 0 and works its way upward to today's day.

    e.g.

    declare @dt datetime

    set @dt = getdate()

    select cast(@dt as float)

    The confirmation:

    declare @dt datetime

    set @dt = '1900-01-01 00:00:00.000'

    select cast(@dt as float)

  • Are datetime datatype stored as floats or is more like date is one integer (the number of days from 1/1/1900) and the time another integer (the number of 1/300th second ticks from beginning of day) with the two integers together composing the 8 data-bytes that composes the date/time?

  • It is stored as a float. The portion left of the decimal is the date and the right portion is the time and seconds.

  • 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

  • Thanks for the detailed replies. That certainly explains the behavior.

    CG

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply