Fog Creek Software
Discussion Board




dtPicker and Null values

We've got a change through that I just don't like.  In our VB6 app we use the dtPicker control for the dates.  This worked fine since we initialized all dates with default values.

Then the client decided that one of the dates had to be optional.  The user must explicitly enter a date otherwise it is left blank.

On the GUI side this is no problem, the control has a checkbox property that displays a checkbox.  The user can leave the box unchecked and no date is selected.

The problem is how to represent this in the SQL Server 7 database.  Originally the field was of DateTime type, as you would expect.

It makes sense to me that the field should be nullable DateTime now, but the developer of the change insists that we now need to change the field to VarChar and store the date as a String.  He says that the nullable field option is not viable, but he cannot remember why not.

Conceptually this seem insane to me, especially when you consider that this is a multinational app (the server is dd-mm the client is mm-dd) .  The situation I am in now is that the work has already been done, so it is probably going to go ahead unless there is a real reason to go back and change it.

This seems a common enough problem, so I was wondering if anybody else has experience of it.  Is there really a compelling reason why you must use a VarChar rather than a nullable DateTime?  Can we get away with storing dates as text in a multinational application?  Has anybody managed to find a tidy solution for themselves?
    
[1] http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cmctl298/html/vbprocheckboxproperty.asp

Frustrated Maintenance Programmer
Wednesday, July 21, 2004

Don't store the date as text.  You'll be creating more work for yourself.  A NULL date should work fine.

Thank you Mario! But our Princess is in another Castle
Wednesday, July 21, 2004

If you can't (for political or technical reasons) use a Null and have to go with a Varchar field, _don't_ store the date/time as a localized string, use a standardized format for the date/time. I like the W3.org standard (which is also based on the ISO standard): YYYY-MM-DDThh:mm:ss.sTZD

See http://www.w3.org/TR/NOTE-datetime for a full writeup

(and why Varchar for the field, char seems more logical unless _most_ of the time the field will be empty)

RocketJeff
Wednesday, July 21, 2004

Thanks for the replies.  For political reasons I won't be able to change any of this unless I can prove it is absolutely essential.

If somebody knows of a specific scenario that will show this approach for what it is, I would be very grateful.

FSM
Wednesday, July 21, 2004

Got my full name wrong.  Posting anonymously in case the developer in question happens to be lurking.

FMP
Wednesday, July 21, 2004

Why not store an empty date as 1-1-1900, then you know it was meant to be null...

Chris Peacock
Wednesday, July 21, 2004

Well, as most seem to realize in this thread , you can’t just go and change a date type field to string.

Virtually all of your code that deals with dates will crap out. Any query to “test” to overdue dates, day calculations, overdue bills etc will simply NOT work when saved as a string.

Further, as others have mentioned,  you need to use  date type so the computer can work with any date format the user choices

mm-dd-yyyy
or
dd-mm-yyyy

Or, in fact..whatever format the user likes in the control panel. You can’t even begin to worry about how the pc has the date settings set. To throw out a date type and convert it to string is simply not even remotely close to being a suggestion that a sane software developer COULD EVER suggest! The person suggestion this has such little knowledge of software. (I can’t rant here..but this is just plain silly!).

Especially when the suggestion is for allowing null dates. In other words, this is classic case of the cure being much worse then the disease. You mean, no null dates, but just change the data type instead?

And, yes..for sure..where that date field is used the code will have to be checked, and ensured it works for null stuff..but that is far less work then the RAFT of problems that changing a date field to a string field will cause.


Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn

Albert D. Kallal
Wednesday, July 21, 2004

Can you test the app with different regional date settings?  Try all kinds of different combinations including two / four digit years.

See if the dates get displayed / stored properly.  If the app still works then I guess you don't have a strong case. That said, it still is a crappy way to do it and can be a maintenance nightmare.

What about reports / queries??? - it is a lot harder to do reports on string dates.  For instance you can't easily do date calculations.

DJ
Wednesday, July 21, 2004

Go for the nullable column.  That's the purpose of a null in relational databases -- to indicate an absence of a value.

If, for whatever boneheaded reason, you aren't able to push this through, use the ISO-8601 format (like mentioned above) and store 0000-00-00T00:00:00 as your pseudo-null value.  It will still screw up any reporting (most reporting tools that run against databases know about nulls), but at least it's obvious what it is.  If the value represents something like a birthdate, you're not likely to have a 2004-year old customer, right?

example
Wednesday, July 21, 2004

One more comment -- 0000-00-00T00:00:00 is the C# DateTime datatype's value for it's MinValue static constant.

example
Wednesday, July 21, 2004

Programmer,

<quote>
It makes sense to me that the field should be nullable DateTime now, but the developer of the change insists that we now need to change the field to VarChar and store the date as a String.  He says that the nullable field option is not viable, but he cannot remember why not.
</quote>

Whoever said that is completely incorrect. Changing the datatype to varchar is the WORST THING YOU CAN DO. Handling different regional settings, for one thing, will become ALOT harder.

Use a nullable datetime field.

Note that on the VB 6 side of things, you will need to use a Variant rather than a Date variable. In your Property Lets and Gets for it, only accept Date and Null values - check using VarType (don't check using IsDate, since it will accept strings, Empty and numerics). Raise error 13 if anything else gets passed in. You will probably need to set the variable to Null in Class_Initialize as well, since a variant defaults to Empty (which is a valid date, unfortunately).

<quote>
Why not store an empty date as 1-1-1900, then you know it was meant to be null...
</quote>

Umm, wouldn't storing Null also show that you meant it to be Null (as well as making SQL and reporting easier to write)? Plus, what happens if you ever actually want to store 1-1-1900? In short - not the preferred option.

Seeya
Matthew

mjwills
Thursday, July 22, 2004

Programmer,

One thing to keep in mind is that with the dtPicker control when you untick the checkbox, a date is still visible. This can confuse some users.

There are a variety of solutions, including hiding the date with a label, writing a user control or buying a different control which allows blanking. The CCRP project also wrote a dtPicker replacement - keep away from it, since it has its own problems (Validate events don't always fire etc).

Seeya
Matthew

mjwills
Thursday, July 22, 2004

*  Recent Topics

*  Fog Creek Home