Cacher is the code snippet organizer for pro developers

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