SQLite Forum

Best practice: Save partial dates
Login
Saving partial dates is not a "practice" by any stretch of the meaning of the word, even though you may not be the first ever to need to do it.

If it isn't a "practice", then there can be no "best practice".

You may as well ask "What's considered as the 'correct' way to eat ice-cream vinaigrette?". Hopefully nobody knows.

What you need to consider is the use case, and any potential future calculation you might be doing on it.

If 'twas me...

I would put it in a string of exact length with placeholders, perhaps periods for legibility's sake, so that it makes the ISO8601 form (for example):
yyyy-mm-dd
with placeholder like Asterisk, space, period, or such. Preferably things that will not be trimmed (like spaces) and that will not be confused with real date-time separators, like "-" and ":".

So that the following date representations become:

```
    complete date:    2021-06-29
    year-month:       2021-06-..
    year:             2021-..-..
    only month:       ....-06-..  
    only day:         ....-..-29
```
etc.

This way you can easily make a function or regex to ensure it is of the correct length, correct form, you know where every part of it is by character index and in general can see ata glance while looking over a list of them, which are complete and which miss what parts.

Downside: It'll take around 35% more space on average given a random distribution of incomplete dates, but it may matter much more or much less for your use case, depending on whether you "mostly" have complete dates, or "mostly" incomplete dates, if space is a consideration.