March 21, 2008 at 4:57 pm
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
March 21, 2008 at 5:23 pm
'' 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.
March 21, 2008 at 5:32 pm
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
March 21, 2008 at 6:31 pm
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)
March 21, 2008 at 6:36 pm
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?
March 21, 2008 at 8:18 pm
It is stored as a float. The portion left of the decimal is the date and the right portion is the time and seconds.
March 22, 2008 at 6:45 am
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
March 22, 2008 at 9:55 pm
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