1

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.

mrteeth
  • 153
  • 1
  • 1
  • 8
  • 2
    You should store dates using date formats in the database, not as text. – Gordon Linoff Mar 19 '16 at 16:19
  • Don't go this way: you will have to do much more validation than that: 2015-02-29 is also not a valid date. Use the date data type. Formatting is something you do when you query your data. – trincot Mar 19 '16 at 16:39
  • Hi @trincot, thanks for the response. When you say use the date data type, will it store dates in the YYYY-MM-DD format? – mrteeth Mar 19 '16 at 18:39
  • 1
    @Gordon Linoff, when I use the date format like this: 'dateregistered date not null', I still run into the same problem. It takes any data format. So if I enter 11052015, it comes out as "11052015", when I want it to come out as "2015-05-11". Do you know how I get it to do the later? – mrteeth Mar 19 '16 at 18:39
  • You should not be concerned about the format that the date is stored in. It is binary. Only when you actually query this data for display, you should apply formatting to it. But using the native Date type will give you lots of benefits: faster queries, correct ordering, invalid dates are rejected, dates can be manipulated easily (adding, subtracting, intervals, ...). – trincot Mar 19 '16 at 18:42
  • @trincot, okay I see. However, this means that if someone enters 20-05-2015 into the dateregistered field, it won't come out in the format I want it (YYYY-MM-DD), when viewing the patient table. Is it possible specify the format in the INSERT INTO statement? – mrteeth Mar 19 '16 at 18:49
  • No, and it should not be like that. You should concentrate on fixing your query to make sure it ***will*** *come out in the format I want it (YYYY-MM-DD)* – trincot Mar 19 '16 at 18:52
  • I should add that SQLite does not have a separate date format like other database engines, but you can store it as numerical value, which comes down to the same thing really. – trincot Mar 19 '16 at 18:56
  • @trincot, I understand now. I just tested out my table in my SQLite, I noticed when I enter data in the YYYY-MM-DD format, with single quotes around it, it displays in the aforementioned format, but without the single quotes, it just displays the year. Thanks for the help. – mrteeth Mar 19 '16 at 19:03
  • Now try entering 2015-02-30. What happens when you select? You get a valid date? I think you are going the wrong way here. I don't feel I have helped you at all, as you have actually ignored most of what I have said. ;-) – trincot Mar 19 '16 at 19:06
  • @trincot, when I enter "2015-02-30", it displays the date as "2015-02-30", which is not a valid date as you say. Sorry, I got ahead of myself and thought I solved the problem. – mrteeth Mar 19 '16 at 21:33
  • In the answer I posted I have documented how I would do it. Have a look, and tell me if this would work for you. – trincot Mar 19 '16 at 21:40

2 Answers2

0

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

trincot
  • 317,000
  • 35
  • 244
  • 286
  • Hi @trincot, thanks for taking the time out to showing me this. This is a good solution. Sorry, that I didn't listen to your advice up there, I thought I had things figured out. :/ – mrteeth Mar 19 '16 at 21:59
  • I've tried your solution about and it doesn't work. I used `CREATE TABLE patient( dateregistered int not null );`
    and `this insert into patient values (julianday('2015-12-31'));`
    this simply results in the dateregistered column containing "2457387.5". What am I doing wrong here.     – mrteeth Mar 20 '16 at 00:21
  • Look at point 3 in my answer. That is the way to select a date from that table ***and format it in human readable form***. You really need to try to understand. Read again the first paragraph of my answer. Don't mix up the database format (which is tailored for fast operations, calculations, intervals, indexing and ordering) and the query format (which is tailored for human eyes). They are completely different concepts. – trincot Mar 20 '16 at 01:00
  • I added point 4 to my answer, which gives you a way to get human formatted date out of a view, without the need to call the `date()` function on the value stored in the table. – trincot Mar 20 '16 at 01:12
  • sorry for the late reply, I'm posting from the UK. So, you're saying don't be concerned what it looks like in the database, it only matters when you're querying the data and displaying it to view? Thanks for the further explanation. I'm fairly new to SQL, so I'm still trying to understand these concepts. – mrteeth Mar 20 '16 at 09:30
  • Another question, if I wanted to use the date() fuction in INSERT INTO, whould I specify it like this: `INSERT INTO patient VALUES (date('YYYY-MM-DD', '2015-03-20'));`? – mrteeth Mar 20 '16 at 09:36
  • No, do like I wrote in step 2 of my answer. I think I explained it already. `date` is for when *retrieving* dates (step 3, in human readable format), and `julianday` for inserting dates (step 2). – trincot Mar 20 '16 at 09:53
  • okay, I see. No problem. Slightly off-topic, is there a reason why there isn't a Date data type in SQLite? – mrteeth Mar 20 '16 at 10:17
  • I honestly have no idea why. Maybe because the makers wanted to keep it "light". – trincot Mar 20 '16 at 23:04
-1

There are 3 type of Data Type in SQL(MySQL) following:

  1. numeric
  2. Text
  3. Date

T0 Define a column with date type, you can use DATE data type

Example:

create table(NAME varchar(30), Date_of_Birth date)

Ripunjay Tiwari
  • 1