GuidesTry Free
Guides

Client Side SQL 101 Cheatsheet

Suggest Edits

Please note, for a full list of functions, please see https://github.com/AlaSQL/alasql/wiki/Sql

🚧

For functions, case matters (they must be capitalized)

For example, to get the day from a datetime, you must do DAY(createdTime), not Day(createdTime)

Converting strings to dates

To convert strings to dates use the CONVERT function as follows:

CREATE TABLE IF NOT EXISTS cities1 (city string, pop number, created number); 
INSERT INTO cities1 VALUES ('Paris',2249975, 1674011459),('Berlin',3517424, 1624007507),('Madrid',3041579, 1614007507) ; /* This assumes the unix timestamps is in seconds from the unix epoch */
/* This assumes the unix epoch is measured in seconds, and thus should be multiplied by 1000*/
SELECT *, CONVERT(datetime, `created` * 1000) as `cast time`  FROM cities1 WHERE pop < 3500000 ORDER BY pop DESC;

Comparing two dates

To compare two dates use the DIFFDATE function as follows. The function takes three arguments, the first argument is cast into a date and subtracted from the second argument. The third argument can be one of:
'SECONDS', 'MINUTES', 'HOURS', 'DAYS', 'WEEKS', 'MONTHS', 'YEARS' 

CREATE TABLE IF NOT EXISTS cities2 (city string, pop number, created string, renewed string); 
INSERT INTO cities2 VALUES ('Paris',2249975, "2013-03-04", "2013-03-05"),('Berlin',"2016-10-04", "2016-11-15"),('Madrid',"2018-02-20", "2019-03-05") ; 
/* The first date is subtracted from the second. The third argument can be one of: 
'SECONDS', 'MINUTES', 'HOURS', 'DAYS', 'WEEKS', 'MONTHS', 'YEARS' 
*/
SELECT *, DIFFDATE(`created`, `renewed`, 'DAYS') as `difference`  FROM cities2 WHERE pop < 3500000 ORDER BY pop DESC;

Getting the day, month or year from the current date

CREATE TABLE IF NOT EXISTS cities2 (city string, pop number, created string, renewed string); 
INSERT INTO cities2 VALUES ('Paris',2249975, "2013-03-04", "2013-03-05"),('Berlin',"2016-10-04", "2016-11-15"),('Madrid',"2018-02-20", "2019-03-05") ; 
/* The first date is subtracted from the second. The third argument can be one of: 
'SECONDS', 'MINUTES', 'HOURS', 'DAYS', 'WEEKS', 'MONTHS', 'YEARS' 
*/
SELECT DAY(created), MONTH(created), YEAR(created) from cities2

Updated almost 2 years ago