General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>What is the best way to store dates if the day or month might be unknown? I can imagine three separate fields for year, month, and day. I can also imagine a character field for date that might look like "2001/03/??" or something like that. I haven't worked with calendar controls. Do any of them permit a "partially null" date like this?
I think you'll cause yourself a lot of grief if you start "fudging" dates; particularly when creating (user-driven) ad-hoc reports.
If the month/day is uncertain, default to 2001/01/01.
If the day is uncertain, default to 2001/{current month}/01.
At least you will be able to obtain "accurate" YTD/MTD reporting.
As an alternative, do away with the date OR supplement the date with YEAR, PERIOD, DAY fields; if the PERIOD or DAY is "unknown", plug in "00" ... this will help with "Queries" in terms of determining relative quality of the data.
The last thing you want is to have someone assume that a given date is "valid" when it was only a "guesstimate".
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only