Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Datetime Column to Empty String - No Error - Strange Results Expand / Collapse
Author
Message
Posted Friday, March 21, 2008 4:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 27, 2014 10:25 AM
Points: 184, Visits: 218
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
-- id Datetest
-- 1 2008-03-21 15:55:00.710

Update testDt
set datetest = ''
where id = 1

go

-- 1 row affected

Select * from testDt

go

-- Results
-- id Datetest
-- 1 1900-01-01 00:00:00.000

Drop table testDt
go

Thanks
CG
Post #473146
Posted Friday, March 21, 2008 5:23 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 4, 2013 4:22 PM
Points: 480, Visits: 467
'' 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.
Post #473150
Posted Friday, March 21, 2008 5:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 27, 2014 10:25 AM
Points: 184, Visits: 218
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
Post #473151
Posted Friday, March 21, 2008 6:31 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 24, 2014 11:02 AM
Points: 2,278, Visits: 3,065
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)





My blog: http://jahaines.blogspot.com
Post #473161
Posted Friday, March 21, 2008 6:36 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 4, 2013 4:22 PM
Points: 480, Visits: 467
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?
Post #473162
Posted Friday, March 21, 2008 8:18 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 24, 2014 11:02 AM
Points: 2,278, Visits: 3,065
It is stored as a float. The portion left of the decimal is the date and the right portion is the time and seconds.



My blog: http://jahaines.blogspot.com
Post #473174
Posted Saturday, March 22, 2008 6:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 4, 2013 4:22 PM
Points: 480, Visits: 467
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
Post #473201
Posted Saturday, March 22, 2008 9:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 27, 2014 10:25 AM
Points: 184, Visits: 218
Thanks for the detailed replies. That certainly explains the behavior.

CG
Post #473282
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse