Search code examples
sqlsqlitedate-formatsqlitemanager

Creating a date column with date format in SQL

I'm creating a table that contains a date registered column. I want it formatted as "YYYY-MM-DD". I've already attempted this...

CREATE TABLE patient(
    dateregistered text format 'YYYY-MM-DD' not null
);

Now, the problem I'm having is, when I enter a date in a different format (i.e. 11-05-2015), it accepts that date in the table. Shouldn't it be giving me an error message or reformating it to the format I set? (I prefer the later, unless the data entered erroneous i.e. 32-13-20015)

Can someone let me know what I'm doing wrong?

Kind regards.

Solution

  • There are two different things at play:

    1. The format a date is stored in the database
    2. The format a date is displayed with

    It is easy to confuse these two, and even more when using SQLite.

    However, storing a date in the database as a text is not a good idea. This will mean the database can store invalid dates, such as "ABC", but also "2015-13-01" and "2015-02-29".

    Also, databases work faster with numeric representations of dates than strings of 10 characters. With numeric representations you can more easily do date calculations, like for instance adding a day to a date (+1), while with string representations that would be more complex.

    Here is a suggestion:

    1. Date storage type

    Create your table like this:

    CREATE TABLE patient(
    dateregistered int not null
);

    and insert values like this:

    2. Inserting dates

    insert into patient values (julianday('2015-12-31'));

    With this set up, invalid dates will either be turned into valid dates or rejected. For instance:

    julianday('2015-02-29')

    will result in 2015-03-01 being stored in the table. And this:

    julianday('2015-13-20')

    will turn into a NULL value, triggering an error on insert.

    It actually becomes impossible to insert something that is not a valid date.

    3. Querying dates

    You would get dates in readable format like this:

    select date(dateregistered)
from   patient

    The date function takes care of the formatting to YYYY-MM-DD.

    But you can also do calculations, like adding one day:

    select date(dateregistered+1)
from   patient

    Or, if you have also another date, like dateinvited, you can easily get the number of days between these two events:

    select dateinvited - dateregistered
from   patient

    4. Optional: create a view

    If you find it cumbersome to specify date(...) whenever you want to query a date in YYYY-MM-DD format, then create a view that does this for you:

    create view vw_patient as
select date(dateregistered) dateregistered
from   patient

    And now when you select from that view:

    select dateregistered
from   vw_patient

    You'll get a string:

    2015-02-28