Creating a date column with date format in SQL

Question

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.

Answer 1

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