Useful Examples for Dates in SQL
Last updated:Most examples are for MySQL, but will probably work for other RDBMSs too.
Current timestamp for DATETIME and TIMESTAMP fields
in case of MySQL, this can only be used for DATETIME from version 5.6 upwards
CURRENT_TIMESTAMP
Current date for DATE fields
CURRENT_DATE
Current time for TIME fields
CURRENT_TIME
Create a table with a default value for a DATETIME field
CREATE TABLE users (
user_id int NOT NULL,
date_created DATETIME DEFAULT CURRENT_TIMESTAMP
);
Convert a datetime from one format to another:
(This is MySQL-specific)
Say you have a column of type DATETIME called "birth_date"
SELECT DATE_FORMAT(birth_date,'%e/%m/%Y')
FROM ...
This will convert a DATETIME (something like "2005-01-01 00:00:00") to a string formatted as "dd/mm/yyyy".
References: