There is also the fact we are using the SQLite implementation of SQL, which has a few slight differences, one of which is DATE types (from
https://www.sqlite.org/datatype3.html):
---
2.2. Date and Time Datatype
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.
---
The value used for the date is effectively the time value, as you can see below, you can use the level of precision you require, e.g. we dont need hours/mins/secs.
From:
https://www.sqlite.org/lang_datefunc.html
2. Time Values
A time-value can be in any of the following formats shown below. The value is usually a string, though it can be an integer or floating point number in the case of format 12.
YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD
---
For our purposes, we would be dealing with dates as strings, SQLite would still have all the functionality we need for later use like using dates in comparisons (direct comparison, between, before, after, etc.).
You just need to be sure you use the formatting we mention, and we should have also referenced dates correctly for you to use in the SQL code.
Thanks,
Adam.
"Tuan Hoang" <24*9*8*
6@s*u*e*t*u*a*e*u*a*> wrote:
> Hi all,
> I have a question related to data type when defining a new table.
> When I define my table, I realise that we define the attribute "DOB" as DATE, but when I want to enter data into my table, as follows:
> e.g. INSERT INTO Branch(DOB) VALUES ('1995-10-31')
> The value I inserted into the table is a string, so when I did additional test to see whether this data is automatically transformed to DATE data type, I encountered an unexpected result. There are some solutions I came up with after research:
> 1. Use DATE() --> INSERT INTO Branch(DOB) VALUES (DATE '1995-10-31'): the expected result is not 1995-10-31
> 2. According to the textbook, numeric data should be in a quotation mark --> INSERT INTO Branch(DOB) VALUES (1995-10-31): SQL does the subtraction.
> Can anyone help me on this problem? Thank you so much.
