Below are examples of using SQL to return the first day of the month across various DBMSs.
This could be the first day of the current month, or the first day of a month based on a given date.
Continue readingCategory: SQLite
Subtract Months from a Date in SQLite
In SQLite, we can use the DATE() function to subtract one or more months from a date.
For datetime values, we can use the DATETIME() function.
SQLite CASE Expression
In SQLite, the CASE expression compares a list of conditions and returns one of multiple possible expressions.
The CASE expression is included in the SQL standard (ISO/IEC 9075), and most major RDBMSs support it.
Get the First, Second, Third, or Fourth Monday of a Year in SQLite
We can use SQLite’s DATE() function to return the first, second, third, fourth, etc instance of a given day within a given year.
We can use the same technique to return the first Monday of each month in the year.
It’s not limited to Monday though. The same techniques can be used on Tuesday, Wednesday, Thursday, Friday, etc.
Continue readingSubtract Seconds from a Time Value in SQLite
In SQLite, we can use the TIME() function to subtract one or more seconds from a time value.
For datetime values, we can use the DATETIME() function.
How to Delete Duplicate Rows that have a Primary Key in SQL
The following examples use SQL to delete duplicate rows that have a primary key or unique identifier column.
Specifically, the examples delete all duplicates except one. So for example, in the case of three identical rows, it deletes two of them and keeps one.
Continue readingFix “cannot add a STORED column” in SQLite
If you’re trying to add a generated column in SQLite, and you’re getting the “cannot add a STORED column” error, it’s probably because you’re trying to add a stored column.
In SQLite, we can only add virtual columns to existing tables.
Continue readingReturn Rows that Contain Only Non-Alphanumeric Characters in SQL
Here are examples of using SQL to return rows that contain non-alphanumeric characters in various DBMSs.
Non-alphanumeric characters include punctuation characters like !@#&()–[{}]:;',?/* and symbols like `~$^+=<>“, as well as whitespace characters like the space or tab characters.
Subtract Hours from a Time Value in SQLite
In SQLite, we can use the TIME() function to subtract one or more hours from a time value.
For datetime values, we can use the DATETIME() function.
Return Only Numeric Values in SQL
If you ever need to use SQL to return just the numeric values in a character column, the method you use will largely depend on your DBMS. Most DBMSs support regular expressions (regex), but some don’t. And some include functions that can detect numeric values.
Here are examples of how to do it in the more popular DBMSs.
Continue reading