Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003

    Cool Unanswered: Date Fields - To Null or Not to Null

    I am new to MS-SQL and I am doing some testing with converting a few of our current tables from Pervasive 2000i format. The first thing I run into is problems with data fields. We have a lot of date fields in our various tables that have no date (00/00/0000) which is correct. It could mean for instance that something has not happened yet (i.e. Posting Date).

    Ok here is my question: Is it better not to allow Nulls in Date fields? If so, what is the best practice to have for the default (i.e. 01/01/1901) date?

    We use Crystal Reports and a RAD tool to produce our many complex reports. So this will be a very important decision.



  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    My vote is to allow nulls. I never liked the idea of putting in a bogus date (9/9/9999) to represent a missing date value. I've always thought it was kind of sloppy.


  3. #3
    Join Date
    Feb 2003
    Brisbane, Australia
    I agree... Go NULL all the way

    Originally posted by blindman
    My vote is to allow nulls. I never liked the idea of putting in a bogus date (9/9/9999) to represent a missing date value. I've always thought it was kind of sloppy.


  4. #4
    Join Date
    Mar 2003
    You have 3 options as I see it..

    1) Allow Nulls
    Pros: Quick
    Cons: Requires 3 valued logic, not very indexable

    2) Define and Document a "bogus" date as blindman called it
    Pros: You actually have a value that can use an index
    Cons: Documentation and logic

    3) Project away the date field into a new table using a one-to-none or one-to-one constraint.
    Pros: Nice and relational...
    Cons: Requires an extra join.

    Depending on what option you do go for, don't forget that you can setup views to help out ie: ViewValidDates (Good Dates) and ViewNonValidDates (NULLs, BogusDate etc...)

  5. #5
    Join Date
    Apr 2002
    Toronto, Canada
    definitely use nulls

    byrmol, since when can you not index a column that has nulls?

    or are you implying that the index is never used? i know i've created indexes on columns with nulls (i was on sql 7) but i never bothered to dig into the execution plan to see if the index was actually being used

    the "default" date will get you into WAY more trouble than nulls

    that is because the "default" date forces upon you a different "third-value logic" and therefore since you have to code for three values anyway, it is better to use nulls, since there are cases (certain sql constructions) where nulls are easier than having always to remember to add "where datefield <> 'xxxx-xx-xx'" to each and every query, which, i guarantee you, somebody (usually yourself) will forget to do

    as for the third option, normalizing the date out into a separate table, just so there's no column in the main table with a null in it -- i know where you're coming from but i really don't think you get much from this, because although you can bring in the date with a join, imagine doing that for all the fields in a typical application which can go null, and suddenly you have to code twenty-seven-table-join monstrosities just to get an "original" row

    rudy | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2003
    null sounds good to me.....
    If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts