In the context of this exercise, what does the strftime() function do?
The strftime() function is used to format date type values, and allows us to do useful things such as extract individual values like the month, year, or day from the date value.
For example, we can obtain these values like so, from some date.
/* Returns the day of month */
strftime("%d", date)
/* Returns the month */
strftime("%m", date)
/* Returns the year */
strftime("%Y", date)
SQLite comes with a strftime() function - a very powerful function that allows you to return a formatted date.
It takes two arguments:
strftime(format, column)
For
strftime(__, timestamp):
* `%Y` returns the year (YYYY)
* `%m` returns the month (01-12)
* `%d` returns the day of the month (1-31)
* `%H` returns 24-hour clock (00-23)
* `%M` returns the minute (00-59)
* `%S` returns the seconds (00-59)
if `timestamp` format is `YYYY-MM-DD HH:MM:SS` .
All this information was in How to Hack Hacker News Project
Codecademy is the Best… 
Hi cloud
could you explain how Codecademy came up with this answer given that nothing in this solution has a table?
SELECT january.user_id
FROM january
LEFT JOIN february
ON january.user_id = february.user_id
WHERE february.user_id IS NULL;
The initial question is: Use a left join to combine january and february on user_id and select user_id from january .
Add the following WHERE statement to find which users played songs in January, but not February:
WHERE february.user_id IS NULL
WITH january AS (
SELECT *
FROM plays
WHERE strftime("%m", play_date) = '01'
),
february AS (
SELECT *
FROM plays
WHERE strftime("%m", play_date) = '02'
)
There were two temporary tables created called january and february.
Does anyone know how to get the query to run correctly? I had input the following: SPOILER
which should be literally the same thing as the solution, but whenever i run the query, it brings up nothing…not sure if it’s me or a glitch or something 
Hello @jyrrin,
In your last select statement, try specifying the table the user_id should be selected. It could be from january.user_id or february.user_id.
WITH january AS (
SELECT *
FROM plays
WHERE strftime("%m", play_date) = '01'
),
february AS (
SELECT *
FROM plays
WHERE strftime("%m", play_date) = '02'
)
SELECT january.user_id -- prefix user_id with table name
FROM january
LEFT JOIN february
ON january.user_id = february.user_id
WHERE february.user_id IS NULL;
@coffeencake You’re my hero!
Thanks man, that was very useful.
Hi, could someone help:
the query is
But it returns not only 2020 but also other years. I can’t find the mistake. Thank you a lot.
@smlaroo7178396734 reviews_2020 is created as a temporary table to refine the 2020 data from the reviews table - you would then have to specify that you were using that review_2020 table in the second half of the code (instead of the reviews table) which would then return results from that 2020 specific data.
WITH reviews_2020 AS (
SELECT *
FROM reviews
WHERE strftime(’%Y’, review_date) = ‘2020’
)
SELECT *
FROM reviews_2020
JOIN places
ON reviews_2020.place_id = places.id
Right, thanky you very much
Hi all,
Trying to extract year from the Multiple Tables section in Lesson 3 using the following query:
SELECT order_id, strftime(‘%Y’, purchase_date)
FROM orders;
but getting null values.
Recall seeing that the default date format in SQL is YYYY-mm-dd, which might explain why I get null values (as dates are inputted as dd-mm-YYYY, and sometimes as dd-m-YYYY). However, the orders ‘database schema’ does list the variable type as DATE.
Any way to extract year, or am I missing something in my query?