Useful Examples for Dates in SQL

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:

sql date db

Archive