NULL DATETIME VALUES IN AN UPDATE STATEMENT

  • 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?

  • 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: http://dineshasanka.spaces.live.com/

  • 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

  • Thanks for the useful info!!

  •  

    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