Date

 SELECT *
 FROM users
 WHERE birthday BETWEEN 1980 AND 1990
 ORDER BY birthday DESC;

https://www.codecademy.com/paths/data-science/tracks/sql-basics/modules/dspath-sql-projects-and-applications/projects/user-segmentation-prj

When checking the table schema of the users table, it says birthday column is a TEXT column. But somehow I could get a result as how I intended that I wanted to see the data of users who are born between 1980-01-01 and 1989-12-31 (both inclusive).

My question here is

  1. How can SQL return the output even though I didn’t put between ‘1980’ and ‘1989’ but instead putting between 1980 and 1989? Shouldn’t it return nothing since I am making queries on the wrong data type?

  2. I’ve learnt that BETWEEN clause goes up to the second value during this course(Data science - SQL Basics). The example given during the course is written below.

SELECT *
FROM movies
WHERE name BETWEEN 'A' AND 'J';

So therefore the terminal should show me the result of movies which all have name starting from the alphabets between A to J but only if the movie’s name is ‘J’ it will be included in the terminal.
How does this loigc apply to the date/integer data type? Or is this logic only valid with string data?

  1. How does SQL calculate the start of a day and the end of a day?
SELECT email, birthday
FROM users
WHERE birthday >= '1980-01-01'
  AND birthday <= '1989-12-31';

Does this code mean bring data which matches birthday column data from 1980-01-01 00:00:00 to 1989-12-31 23:59:59?

Hey @nayunsung3562491092!

Ah yes, but remember, if you do 1 == “1”, you get true. The same sort of concept is displayed here, even though “1980” is a string, it is equal to a number.

Different rules apply to different columns of the table. The person creating this database may have set a different rule for the “date” column then the movie column.

I don’t think it does, or at least not unless you specify. You’ll get all the dates between ‘1980-01-01’ and ‘1989-12-31’. It doesn’t matter what time it is, it will only get included if its between the end of the first day and the start of the second. So, for example, ‘1989-12-30 23:59:59’ would be included but ‘1989-12-31 00:00:00’ would not be.

1 Like

Hi stevencopeland! Thank you so much for your super clear explanation for my questions.

So SQL regards those two data as the same data type when it comes to numbers? If it that so, it’s quite different from Python then!