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
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?
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?
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?
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.