MySQL Date Or Time Function Part-2

DATE Function:- The DATE Function is used for extracting date from a given date-time expression.

Syntax:- DATE(expression)

Example:-

SELECT DATE(“2023-02-06”);

Output:- 2023-02-06

SELECT DATE(“2023-02-06 01:35:41”);

Output:- 2023-02-06

SELECT DATE(“Today date is 2023-02-06”);

Output:- NULL

DATE_ADD Function:- DATE_ADD Function is used for returning the date time after adding a date or time interval.

Syntax:- DATE_ADD(date, INTERVAL value add unit)

We can use following add Unit:-

MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH

Example:-

SELECT DATE_ADD(“2023-02-06”, INTERVAL 15 DAY);

Output:- 2023-02-21

SELECT DATE_ADD(“2023-02-06”, INTERVAL 5 YEAR);

Output:- 2028-02-06

DATE_FORMAT Function:- DATE_FORMAT Function is used for formatting dates from a given specified value. The specified value may be following:-

%a          (Sun to Sat)

%b      (Jan to Dec)

%c          Numeric month name (0 to 12)

%D         Day of the month as a numeric value

%d          Day of the month as a numeric value

%e          Day of the month as a numeric value

%f           Microseconds (000000 to 999999)

%H         Hour (00 to 23)

%h          Hour (00 to 12)

%I           Hour (00 to 12)

%i           Minutes (00 to 59)

%j           Day of the year (001 to 366)

%k          Hour (0 to 23)

%l           Hour (1 to 12)

%M        (January to December)

%m        Month name as a numeric value (00 to 12)

%p          AM or PM

%r           Time in 12 hour AM or PM format (hh:mm:ss AM/PM)

%S          Seconds (00 to 59)

%s          Seconds (00 to 59)

%T          Time in 24 hour format (hh:mm:ss)

%U         Week where Sunday is the first day of the week

%u          Week where Monday is the first day of the week

%V         Week where Sunday is the first day of the week

%v          Week where Monday is the first day of the week

%W      (Sunday to Saturday)

%w         Day of the week where Sunday=0 and Saturday=6

%X          Year for the week where Sunday is the first day of the week.

%x          Year for the week where Monday is the first day of the week.

%Y          Year as a numeric, 4-digit value

%y          Year as a numeric, 2-digit value

Syntax:- DATE_FORMAT(date, format)

Example:-

SELECT DATE_FORMAT(“2023-02-06”, “%M %d %Y”);

Output:- February 06 2023

SELECT DATE_FORMAT(“2023-02-06”, “%W %M %e %Y”);

Output:- Monday February 6 2023