DATE()
STRFTIME();
--convert to UK DATE FORMAT
SELECT *, STRFTIME("%d/%m/%Y", ordered_on) AS UK_date FROM orders;
--get the date from 7 days ago
DATE("now","-7 days");
DATE("now","-7 months");
DATE("now","-7 years");
SELECT * FROM orders WHERE status = "placed" AND ordered_on = DATE("now","-7 days");
--get orders between dates
SELECT COUNT(*) FROM orders WHERE ordered_on
BETWEEN DATE("now", "-7 days", "-7 days")
AND DATE("now","-1 day", "-7 days");
-- get shipped orders from yesterday
SELECT count(*) AS shipped_yesterday FROM orders WHERE status = "shipped" AND ordered_on = DATE("now", "-1 day");
SELECT DATE("now");
-- get orders for todays date
SELECT * FROM orders WHERE status = "placed" AND ordered_on = DATE("now");
SQLite
To get the current date use: DATE("now")
To get the current time use: TIME("now")
To get the current date time: DATETIME("NOW")
MS SQL
To get the current date use: CONVERT(date, GETDATE())
To get the current time use: CONVERT(time, GETDATE())
To get the current date time: GETDATE()
MySQL
To get the current date use: CURDATE()
To get the current time use: CURTIME()
To get the current date time: NOW()
Oracle and PostgreSQL
To get the current date use: CURRENT_DATE
To get the current time use: CURRENT_TIME
To get the current date time: `CURRENT_TIMESTAMP