Menu
MySQL

Date and Time Functions

MySQL provides a variety of date and time functions to work with date, time, and datetime values. These functions allow you to manipulate, format, and calculate date and time values in your queries.

 

 

1. NOW()

The NOW() function returns the current date and time as a datetime value (YY-MM-DD HH:MM:SSYY).

Syntax:

SELECT NOW();
 

Example:

SELECT NOW() AS CurrentDateTime;
 

Result:

CurrentDateTime
2024-08-16 10:35:47

 

 

2. CURDATE()

The CURDATE() function returns the current date as a date value (YYYY-MM-DD).

Syntax:

SELECT CURDATE();

 

Example:

SELECT CURDATE() AS CurrentDate;

 

Result:

CurrentDate
2024-08-16

 

 

3. CURTIME()

The CURTIME() function returns the current time as a time value (HH:MM:SS).

Syntax:

SELECT CURTIME();
 

Example:

SELECT CURTIME() AS CurrentTime;
 

Result:

CurrentTime
10:35:47

 

 

4. DATE()

The DATE() function extracts the date part from a datetime or timestamp value.

Syntax:

SELECT DATE(datetime_value);
 

Example:

SELECT DATE(NOW()) AS DatePart;
 

Result:

DatePart
2024-08-16

 

 

5. TIME()

The TIME() function extracts the time part from a datetime or timestamp value.

Syntax:

SELECT TIME(datetime_value);
 

Example:

SELECT TIME(NOW()) AS TimePart;

 

Result:

TimePart
10:35:47

 

 

Summary of Common Date and Time Functions

FunctionDescription
NOW()Returns the current date and time.
CURDATE()Returns the current date.
CURTIME()Returns the current time.
DATE()Extracts the date part from a datetime or timestamp.
TIME()Extracts the time part from a datetime or timestamp.
YEAR(), MONTH(), DAY()Extracts the year, month, or day from a date.
HOUR(), MINUTE(), SECOND()Extracts the hour, minute, or second.
ADDDATE()Adds a specified number of days to a date.
SUBDATE()Subtracts a specified number of days from a date.
DATEDIFF()Returns the difference in days between two dates.
DATE_ADD()Adds an interval to a date.
DATE_SUB()Subtracts an interval from a date.
EXTRACT()Extracts a part of a date or time.
STR_TO_DATE()Converts a string into a date using a specified format.
DATE_FORMAT()Formats a date according to a specified format.
TIMESTAMPDIFF()Returns the difference between two dates in a given unit.
UNIX_TIMESTAMP()Returns the current Unix timestamp.

 

 

 

 

MySQL