February 16, 2005 at 4:59 am
I HAVE A DATETIME FIELD IN A TABLE THAT ALLOWS NULL VALUES. WHEN ANY OF THE RECORDS THAT HOLD A NULL VALUE IN THIS FIELD ARE UPDATED, THE VALUE CONVERTS TO THE DATE "01/01/1900".
WHY IS THIS AND WHAT IS THE BEST WAY TO OVERCOME THIS?
February 16, 2005 at 5:50 am
from the http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp article
To visualize how date/time values are stored, you can think of them as consisting of two parts. The integer portion represents the number of whole days since January 1, 1900. The fractional portion represents the fraction of a day that’s passed since midnight. For example, the date/time value representing noon on January 4, 1900 is stored as 3.5. In this case, 3 represents three full days since the base date and 0.5 represents one half of a day between midnight and noon.
as in your case it is 0. that means date is 1/1/1900
What do you want to display there instead of 1/1/1900?
If u want to display a null , use cast and case
My Blog:
February 16, 2005 at 5:55 am
I find that if you want the field to remain NULL you must explicitly assign NULL to the field. If you are passing an empty set it will save as 1/1/1900. (the above explanation tells why as empty set is interpretted as 0.)
So, just make sure your UPDATE statement says FieldName = NULL.
Five Stones IT Consulting and Development
February 16, 2005 at 7:17 am
Thanks for the useful info!!
February 17, 2005 at 3:19 pm
To illustrate, look at the following examples:
1) SELECT CONVERT(INT, '') AS [CONVERT(INT, '')], CONVERT(DATETIME, '') AS [CONVERT(DATETIME, '')]
2) SELECT CONVERT(INT, NULL) AS [CONVERT(INT, NULL)], CONVERT(DATETIME, NULL) AS [CONVERT(DATETIME, NULL)]
The results are
CONVERT(INT, '') CONVERT(DATETIME, '')
0 1900-01-01 00:00:00.000
CONVERT(INT, NULL) CONVERT(DATETIME, NULL)
The result is actually null, null.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply