locked
NULL in DateTime RRS feed

  • Question

  • Hello,

    I am working on a USERS table which has a few DateTime field. For example:

      LastLock datetime not null,

      PasswordAttemptStart datetime not null

    The problem is that these fields might be null.

    Should I allow then to be null or use the Minimum SQL Date?

    In my application I can check the columns for NULL or Minimum SQL Date.

    But what would be the best approach?

    Thank You,

    Miguel

    Tuesday, July 17, 2012 9:12 AM

Answers

  • First of all, when you make a column nullable, you should have a clear understanding of what NULL means. I too often see table definitions where the only non-nullable column is an IDENTITY column, and I ask my self "what does it mean that the customer's name is NULL?"

    Now, if we take a column called "LastLock" in a Users table, it is not difficult to understand what NULL means: the user have never been locked out. In this case you should use NULL to signify this. Now, before I go on, I should add that this is not an uncontroversial issue, and I know there are very reputable persons in the RDMBS sphere who thinks that NULL is a mistake. I disagree on that point, and I find their position untenable.

    The way I see it NULL is an out-of-band value. If you sweep NULLs under the carpet, the need for out-of-band values does not go away. If we see that LastLock is 1900-01-01, we can assume that this means the user has never been locked out, because there were no computers(*) on that date. But if you want to check for users who have never been locked out, you need to know which magic value you used. Was it 1900-01-01 (the smallest value for smalldatetime), 1899-12-30 (Day 0 in Excel other tools?), 1753-01-01 (smallest value for date time), 1970-01-01 (start value for time in Unix), 0001-01-01 (smallest value for date and datetime2) or something else? The great thing with NULL is that there is only one NULL to choose from.

    Of course, you need to master three-valued logic to use NULL, but that is not rocket science.

    (*) Charles Babbage and Ada Lovelace would disagree, though.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Hasham NiazEditor Tuesday, July 17, 2012 9:49 AM
    • Marked as answer by MDMoura Tuesday, July 17, 2012 9:50 AM
    Tuesday, July 17, 2012 9:43 AM

All replies

  • Hi MD !

    May be you need to change your table schema to allow NULL values to be inserted in 'LastLock' and 'PasswordAttemptStart' column.

    Selecting MIN date for these columns will complicated things if you have rows that originally have MIN value date and those which you have forced to have MIN value date. You need some sort of distinction to separate these rows then.

    Thanks, Hasham Niaz

    Tuesday, July 17, 2012 9:25 AM
    Answerer
  • Check the probablity of using this column as a search argument and the number of occurences as NULL for these columns. If its more for both, then its better to use SQL minimum date.

    We might get different openion even, lets wait for that too...

    Tuesday, July 17, 2012 9:26 AM
  • depends on your number of checks/ compare you need to perform on these two fields.

    if you put as NULL, everytime you may need to use isNull(field, '') function to compare/ fetch data with dates.

    if you use default datetime, say in reports you would like to reject all field <> convert(datetime,'') value to get the correct data.

    regards

    joon

    Tuesday, July 17, 2012 9:30 AM
  • First of all, when you make a column nullable, you should have a clear understanding of what NULL means. I too often see table definitions where the only non-nullable column is an IDENTITY column, and I ask my self "what does it mean that the customer's name is NULL?"

    Now, if we take a column called "LastLock" in a Users table, it is not difficult to understand what NULL means: the user have never been locked out. In this case you should use NULL to signify this. Now, before I go on, I should add that this is not an uncontroversial issue, and I know there are very reputable persons in the RDMBS sphere who thinks that NULL is a mistake. I disagree on that point, and I find their position untenable.

    The way I see it NULL is an out-of-band value. If you sweep NULLs under the carpet, the need for out-of-band values does not go away. If we see that LastLock is 1900-01-01, we can assume that this means the user has never been locked out, because there were no computers(*) on that date. But if you want to check for users who have never been locked out, you need to know which magic value you used. Was it 1900-01-01 (the smallest value for smalldatetime), 1899-12-30 (Day 0 in Excel other tools?), 1753-01-01 (smallest value for date time), 1970-01-01 (start value for time in Unix), 0001-01-01 (smallest value for date and datetime2) or something else? The great thing with NULL is that there is only one NULL to choose from.

    Of course, you need to master three-valued logic to use NULL, but that is not rocket science.

    (*) Charles Babbage and Ada Lovelace would disagree, though.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Hasham NiazEditor Tuesday, July 17, 2012 9:49 AM
    • Marked as answer by MDMoura Tuesday, July 17, 2012 9:50 AM
    Tuesday, July 17, 2012 9:43 AM
  • Try to put this instead

    CONVERT(datetime,'01-01-1900',105)

    and later on when you see such date, you can just disregard it!


    Many Thanks & Best Regards, Hua Min

    Tuesday, July 17, 2012 9:45 AM
  • If we see that LastLock is 1900-01-01, we can assume that this means the user has never been locked out, because there were no computers(*) on that date. But if you want to check for users who have never been locked out, you need to know which magic value you used. Was it 1900-01-01 (the smallest value for smalldatetime), 1899-12-30 (Day 0 in Excel other tools?), 1753-01-01 (smallest value for date time), 1970-01-01 (start value for time in Unix), 0001-01-01 (smallest value for date and datetime2) or something else? The great thing with NULL is that there is only one NULL to choose from.

    Yes, that was why I posted this question.

    I always start to create a table with all fields as "NOT NULL".

    Then I go one by one and see if makes sense to allow NULLS. Most of the times they remain NOT NULL.

    I understand the reasons why some people don't like to use NULL on these situations (Example: LastLock).

    But the other option (using min value) is much less logic than NULL.

    And of course, as you say, there is only one NULL and MinDate there are many.

    For example, I am using C# with this database and MinDate is different there.

    Thank You,

    Miguel

    Tuesday, July 17, 2012 9:50 AM