Problems with BETWEEN dates operator

Question

I am practising and experimenting with different syntax of SQL BETWEEN operator in regards to dates from the "https://www.w3schools.com/sql/sql_between.asp"

This is the Order table in my database:

LINK: https://www.w3schools.com/sql/sql_between.asp

The query is fetching the orderdates between a given condition of 2 dates.

These are the two main syntax versions (according to w3schools):

SELECT *
FROM Orders
WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;

and:

SELECT * 
FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

The output that we get on typing the above two queries from the Orders table

Number of Records: 22 (out of 196 records). Yes this is correct.

Now I am experimenting with this syntax versions.

CASE #1:

SELECT * 
FROM Orders
WHERE OrderDate BETWEEN #1996/07/01# AND #1996/07/31#;

Result of case #1: 22 (same as the above syntax)

In the SQL try it out editor(https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_between_date&ss=-1) they are stating that this SQL statement is not supported in the WebSQL database.The example still works, because it uses a modified version of SQL. WHY SO?

Zack · Accepted Answer · 2019-04-22T14:24:19.137

If you're using the W3Schools Tryit editor in Chrome, you're using WebSQL, which is basically SQLite.

SQLite doesn't have a date/time format, so is probably storing the date values as strings formatted in the ISO-8601 format (see this answer for more information).

Other database systems (e.g. Oracle, Microsoft SQL Server, Postgres, MySQL) have built-in date formats, and you generally represent them as strings (enclosed in single quotes). For example: '1997-07-01' (depending on the specific RDBMS, there might be more specific considerations).

The format that uses pound signs (e.g. #7/1/1997#) is unique to Microsoft Access (see this answer for more information).

Bottom line: Dates are generally enclosed in single quotes. You're best off sticking to the ISO-8601 standard (e.g. 1997-07-01).

If you're learning SQL, there are other resources out there besides W3Schools. I would recommend downloading an open-source RDBMS like Postgres or MySQL, setting up a sample database, and working on some queries. Challenge sites like codewars might also be helpful

One more thing: Don't use BETWEEN for dates. Use >= and <, to make sure you're not excluding dates with a time portion. For more information, read this blog.

Problems with BETWEEN dates operator

1 Answers1