Post reply

Date field default value

  • April 27, 2009 at 6:53 am

    #129619

    We are in discussion on whether to set default value of a date to Null to a bogus data such as '01/01/1900'. I have been looking on the internet for guidance about Null with date but only things I have found are general statements about Null. I know can cause problems with coding when not expecting them. If you have a more current books or documents on best practice for database designs I would appreciate any help. I have found a lot of references to Cobb.

    In addtion, What is a valid definition of Null? We have found couple different definitions as show below:

    1 - used to represent either a missing value or a value that is not applicable in a relational table

    2 - means unknown and not a value

  • April 27, 2009 at 7:21 am

    #984330

    Bridget Elise Nelson (4/27/2009)

    What is a valid definition of Null? We have found couple different definitions as show below:

    1 - used to represent either a missing value or a value that is not applicable in a relational table

    2 - means unknown and not a value

    Click here BOL-Null Values for the official definition.

    Edited to fix url link.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • April 27, 2009 at 7:36 am

    #984338

    What do you mean about BOL?

    Thanks for response.

  • April 27, 2009 at 8:02 am

    #984356

    Bridget Elise Nelson (4/27/2009)

    What do you mean about BOL?

    Thanks for response.

    BOL is SQL Server Books OnLine.

    This is the built-in help file for SQL Server. You can access it from your start menu | Programs | Microsoft SQL Server 2005 | Documentation and Tutorials | SQL Server Books Online. This should be your #1 stop for finding things out about SQL Server.

    Note that the bolded part in my previous message is a hyperlink - just click it to open the Microsoft internet-based BOL for that topic.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • April 27, 2009 at 8:08 am

    #984361

    i personally prefer to set it as null.easier on the eye when looking through the database and working out whether it has a valid date against it or not.

    but i dont think it really matters. 01/01/1900 is a nothing date anyway

  • April 27, 2009 at 8:12 am

    #984366

    davidandrews13 (4/27/2009)

    i personally prefer to set it as null.easier on the eye when looking through the database and working out whether it has a valid date against it or not.

    but i dont think it really matters. 01/01/1900 is a nothing date anyway

    It makes a difference when you start trying to do grouping operations by date.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • April 27, 2009 at 8:17 am

    #984373

    Depending on the size of your project and what exactly you're using the information for. However, handling of NULLs should be a common, and expected, practice. Furthremore, you don't want your programmer or your end user to think that 01/01/1900 actually means anything other than "I don't know this date, I can't know this date, I can't make assumptions about this date".

    Also, if other people after you will be doing work with this database, NULL will make more sense to them than 01/01/1900.

    ---
    Dlongnecker

  • April 27, 2009 at 8:20 am

    #984376

    WanyeS, your link looks broken. I also couldn't find the BOL NULL page myself.

    The wiki page is a pretty good read though -> http://en.wikipedia.org/wiki/Null_(SQL)

    ---
    Dlongnecker

  • April 27, 2009 at 8:27 am

    #984380

    dlongnecker (4/27/2009)

    WanyeS, your link looks broken.

    Whoops. Thanks, I fixed it above.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply