Date Format as Text String?

Language Help SQL
1

There has already been a question on the Codecademy forums regarding date format in SQL, but it didn’t seem to answer the question sufficiently. The last comment was someone saying they set their date as ‘pickles’ and that creating a column with a DATE format (as opposed to INTEGER or TEXT) is useless.

Example:
CREATE TABLE holidays
(id INTEGER, holiday_name TEXT, holiday_date DATE);
INSERT INTO holidays
(id, holiday_name, holiday_date)
VALUES
(1, ‘Christmas’, ‘2023-12-25’);

In the example above, when the first row (Christmas) is being inserted the date is formatted as a text string. Codecademy seems to recommend this format for dates when using the INSERT INTO clause, but then what is the point of setting the column as DATE instead of TEXT? The INSERT INTO date value can’t be set as 2023-12-25 or 2023/12/25 because then SQL interprets that as a mathematical equation.

Thank you for assistance on this question.

2

Do you have a link to the lesson where it specifies this?
It’s my understanding that when inserting values into the table it’s going to be read as a string. (maybe that’s what they’re referring to?)

I’m also assuming you’re referring to SQLite. So, when querying the table, you can use STRFTIME() to format the date in your query.

SELECT id, order, STRFTIME(%d%, %m%, %y%, sale_date) as 'sale_date_formatted'
from blah;

or something like that.

There’s also the docs:
https://www.sqlite.org/lang_datefunc.html

3

Here’s the lesson where it specifies a text string for date:
https://www.codecademy.com/courses/learn-sql/projects/learn_sql_create_table

Based on the lesson you can probably tell I’m not very far into learning SQL so I don’t quite understand the example that you gave, but it looks like you are, in a way, forcing the date to be formatted to what you want as opposed to SQL having a default date format. Is that correct?

I think what I am trying to say is, if there is no default date format in SQL in terms of how it displays values entered in a DATE formatted column, then what is the point of identifying a column as DATE as opposed to TEXT?

4

Dates are stored in a binary format. In this case, the type, DATE stores the data as YYYY-MM-DD.
It depends on the flavor of SQL that one is using. See here.

so when you query it, you can do stuff like this (b/c of how it’s stored):

SELECT id, name
FROM table_name
WHERE birthday = '1977-04-12';

You can use different functions to retrieve info from that column or find the difference between two dates, or CAST it in a different format for your query. It’s all in the docs. Dates and times can be tricky, so some research always helps too.

1 Like
5

The differences between different iterations of SQL do make it complicated for these types of questions, but as one last follow-up to help me (and perhaps others as well) understand the concept:

How would you find the difference between two dates? I don’t know what clause(s) would need to be used, but conceptually something along the lines of: ‘2023-04-13’ - ‘2023-04-12’ = 1

That is something that is possible in SQL, right? Could you give an example of how it might be coded?

6

You could use the DATEDIFF function.
general format is: DATEDIFF ( datepart , startdate , enddate )

Where “datepart” is year (yy, yyyy), month (mm m), etc.

Ex:

SELECT DATEDIFF(year, '2017-4-7', '2019-8-11')
>>2

*That said, I think w/SQLite you have to use JULIANDAY…which is also in the docs.

1 Like