Current Date and Time
MS: SELECT GETDATE()
MY: SELECT NOW()
Optionally: Use CURDATE() for the date only.
Limiting Results
MS: SELECT TOP 10 * FROM table WHERE id = 1
MY: SELECT * FROM table WHERE id = 1 LIMIT 10
Date Field Default Value
MSSQL: DATETIME DEFAULT GETDATE()
MYSQL: DATETIME fields cannot have a default value, i.e. "GETDATE()"
You must use your INSERT statement to specify CURDATE() for the field.
Optionally: Use datatype TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Character Length
MSSQL: LEN()
MYSQL: CHARACTER_LENGTH() Aliases: CHAR_LENGTH(), LENGTH()
Character Replace
MSSQL: REPLACE() works case insensitively
MYSQL: REPLACE() works case sensitively
Trim Functions
MSSQL: LTRIM() and RTRIM()
MYSQL: TRIM()
String Concatenation
MSSQL: CONCATENATION USING + (Does not automatically cast operands to compatible types)
MYSQL: CONCAT(string, string), which accepts two or more arguments.
(Automatically casts values into types which can be concatenated)
Auto Increment Field Definition
MSSQL: tablename_id INT IDENTITY PRIMARY KEY
MYSQL: tablename_id INTEGER AUTO_INCREMENT PRIMARY KEY
Get a List of Tables
MSSQL: SP_TABLES
MYSQL: SHOW TABLES
Get Table Properties
MSSQL: HELP tablename
MYSQL: DESCRIBE tablename
Get Database Version
MSSQL: SELECT @@VERSION
MYSQL: SELECT VERSION()
Recordset Paging
MSSQL: Recordset paging done by client side-ADO (very involved)
MYSQL: Add to end of SQL: "LIMIT " & ((intCurrentPage-1)*intRecsPerPage) & ", " & intRecsPerPage
LIMIT: The first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).
Get ID of Newest Inserted Record
MSSQL: SET NOCOUNT ON; INSERT INTO...; SELECT id=@@IDENTITY; SET NOCOUNT OFF;
MYSQL: Two step process:
1. Execute your statement: objConn.Execute("INSERT INTO...")
2. Set objRS = objConn.Execute("SELECT LAST_INSERT_ID() AS ID")
Get a Random Record
MSSQL: SELECT TOP 1 * FROM Users ORDER BY NEWID()
MYSQL: SELECT * FROM Users ORDER BY RAND() LIMIT 1
Generate a Unique GUID
MSSQL: SELECT NEWID()
MYSQL: SELECT UUID()
