SQLite 3.38.0 was released on 2nd February 2022. With it came some enhancement to the date and time functions.
One of those enhancements is the addition of the UNIXEPOCH() function. This function returns a unix timestamp. That is, the number of seconds since 1970-01-01 00:00:00 UTC.
In SQLite, we can use the following method to calculate the last day of a given month.
We can base this either on the current date, or on some other specific date.
Continue readingSQLite gives us the ability to return the date of the beginning of the month, based on a given date.
This means we can return the date of the first day of the current month, or the first day of the month based on a date that we specify.
This allows us to perform further calculations on the resulting date, like adding a given number of days to it.
Continue readingIn SQLite, we can use the DATE() function to subtract one or more years from a date.
For datetime values, we can use the DATETIME() function.
SQLite doesn’t have a DATEADD() function like SQL Server (or an ADDDATE() or ADD_DATE() function like in some other DBMSs), but it does have the DATE() function that can make additions to a date value.
SQLite also has a DATETIME() function that can make additions to a datetime value (as well as a TIME() function for time values).
Here are three options for returning rows that contain uppercase characters in SQLite.
Continue readingHere are eleven options for returning duplicate rows in SQLite when those rows have a primary key or some other unique identifier column (but you want to ignore the primary key).
This means that the duplicate rows share exactly the same values across all columns with the exception of their primary key/unique ID column.
Continue readingIn SQLite, the INTERSECT operator is used to create a compound SELECT statement that returns the intersection of the results of the left and right SELECT statements. In other words, it combines two queries, but returns only those rows that are returned in both queries.
In SQLite, the EXCEPT operator can be used to create a compound SELECT statement that returns the subset of rows returned by the left SELECT that are not returned by the right SELECT.
In SQLite, the UNION operator creates a compound SELECT statement that returns the results of the left and right SELECT statements. In other words, it combines the results from two queries into a single result set.