MSSQL and MySQL

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()