In 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.
Author: Ian
Fix “ERROR 1250 (42000): Table ‘…’ from one of the SELECTs cannot be used in global ORDER clause” when using UNION in MySQL
If you’re getting an error that reads “ERROR 1250 (42000): Table ‘…’ from one of the SELECTs cannot be used in global ORDER clause” when using the UNION clause in a MySQL query, it’s probably because you’re qualifying a column name with its table name.
This doesn’t work in MySQL.
To fix this issue, either remove the table name or use a column alias.
Continue readingAdd Days to a Date in PostgreSQL
The + operator allows us to add one or more days to a given date in PostgreSQL. We have a few options when it comes to specifying the actual number of days.
11 Ways to Find Duplicate Rows that have a Primary Key in Oracle
Here are eleven options for returning duplicate rows in Oracle Database when those rows have a primary key or some other unique identifier column and you want to ignore it.
Continue readingReturn Rows that Contain Non-Alphanumeric Characters in SQL Server
Here are examples of returning rows that contain non-alphanumeric characters in SQL Server.
Non-alphanumeric characters include punctuation characters like !@#&()–[{}]:;',?/* and symbols like `~$^+=<>“, as well as whitespace characters like the space or tab characters.
SQLite DATEADD() Equivalent
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).
4 Ways to List All Tables in a MySQL Database
Below are four ways to list out the tables in a MySQL database using SQL or the command line.
Continue reading3 Ways to Find Rows that Contain Uppercase Letters in SQLite
Here are three options for returning rows that contain uppercase characters in SQLite.
Continue readingVALUES Clause in SQL Server
In SQL Server, VALUES is a table value constructor that specifies a set of row value expressions to be constructed into a table.
The VALUES clause is often used with INSERT statements to insert data, but it can also be used as a derived table in either the USING clause of the MERGE statement or the FROM clause.
Fix “ERROR 1054 (42S22): Unknown column ‘…’ in ‘order clause'” when using UNION in MySQL
If you’re getting error “1054 (42S22): Unknown column ‘…’ in ‘order clause'” when using the UNION clause in MySQL, it could be because you’re trying to reference an aliased column by its column name.