none
null date field displays 1900-01-01

    Question

  • I have date fields set to allow nulls but display 1900-01-01 when set to blank.  How do I get a date field  display nothing?  I tried

    update table set datefield = '';
    and 
    update table set datefield = convert(date,'');


    Friday, April 28, 2017 10:18 PM

All replies

  • I have date fields set to allow nulls but display 1900-01-01 when set to blank.  How do I get a date field  display nothing?  

    If you know that the date filed is 1900-01-01  or is really null, then it is up to you to set the date field to blank on the screen or you show the real date in the date field on the screen.

    Saturday, April 29, 2017 12:07 AM
  • >>I have date fields set to allow nulls but display 1900-01-01 when set to blank.  How do I get a date field  display nothing?<<

    And the answer to how to do that also depends on what UI you're using and which UI control you're trying to display it in ...


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, April 29, 2017 4:11 PM
    Moderator
  • I'm using VS community 2017.  Unfortunately the date default is 1900-01-01 which is imo, not my first choice, leave it at that.  I would prefer to able to leave a table date field blank instead.  I can of course cater for this before displaying in a Form which I have done.
    Saturday, April 29, 2017 4:28 PM
  • Hi Richard,

    When I said "UI", I meant what UI your application uses, not which version of VS you're using. Is it Windows Forms, WPF, UWP, ASP.NET? Also, how are you getting the data? I assume it's coming from a database (since you showed code to "update table ..."). Are you putting it into a DataSet/DataTable or into a class?

    What you should be focused on though, is the query that *gets* the data. If this is a SQL Database, then you'd have something like this for your date query:

    SELECT datefield = CASE datefield WHEN CONVERT(DATE,'1900-01-01') THEN NULL ELSE datefield END
    FROM table


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, April 29, 2017 11:48 PM
    Moderator
  • I'm using Windows Forms.  
    Monday, May 1, 2017 3:37 PM
  • Did you change your query as I suggested? That's probably all you would need to do.

    Are you using a TextBox or a DataGridView (or some other control), and how are you DataBinding that control?


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, May 3, 2017 1:24 AM
    Moderator