MySQL Date function

When we work on dates, the hardest task is to ensure that the date you insert is formatted to match the format of the date column in the database.

As long as the data contains only the date parts, running the query will not be a problem. However, if time is involved, the situation is a little more complicated.

Before we discuss the complexity of the date query, let's look at the most important built-in date processing functions.

MySQL Date function

The following table lists the most important built-in date functions in MySQL:

function	Description
Now ()	Returns the current date and time
Curdate ()	Returns the current date
Curtime ()	Returns the current time
DATE ()	Extract date part of date or date/time expression
EXTRACT ()	Returns a separate part of the date/time Press
Date_add ()	Add a specified time interval to a date
Date_sub ()	Subtract a specified time interval from a date
DATEDIFF ()	Returns the number of days between two dates
Date_format ()	Display Date/time in a different format

SQL Date Data type

MySQL stores date or date/time values in the database using the following data types:

• DATE-Format YYYY-MM-DD
• DATETIME-format: Yyyy-mm-dd HH:MM:SS
• TIMESTAMP-format: Yyyy-mm-dd HH:MM:SS
• Year-format YYYY or YY

Now () Definition and usage

The now () function returns the current date and time.

Example 1

Here is the SELECT statement:

SELECT now (), Curdate (), Curtime ()

The results are similar:

Now

()	curdate ()	curtime ()
2008-12-29 16:25:46	2008-12-29	16:25:46

Curdate () Definition and usage

The Curdate () function returns the current date.

Example 1

Here is the SELECT statement:

SELECT now (), Curdate (), Curtime ()

The results are similar:

Now

()	curdate ()	curtime ()
2008-12-29 16:25:46	2008-12-29	16:25:46

Curtime () Definition and usage

The Curtime () function returns the current time.

Instance

Here is the SELECT statement:

SELECT now (), Curdate (), Curtime ()

The results are similar:

Now

()	curdate ()	curtime ()
2008-12-29 16:25:46	2008-12-29	16:25:46

DATE () Definition and usage

The date () function returns the day part of a date or date/time expression.

Grammar

Date (date)

The date parameter is a valid day expression.

Instance

Suppose we have the following table:

OrderId	ProductName	OrderDate
1	' Computer '	2008-12-29 16:25:46.635

We use the following SELECT statement:

SELECT ProductName, DATE (OrderDate) as Orderdatefrom orderswhere orderid=1

Results:

ProductName	OrderDate
' Computer '	2008-12-29

EXTRACT () Definition and usage

The EXTRACT () function is used to return a separate part of a date/time, such as year, month, day, hour, minute, and so on.

Grammar

EXTRACT (unit from date)

The date parameter is a valid day expression. The unit parameter can be the following value:

Unit value
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

Instance

Suppose we have the following table:

OrderId	ProductName	OrderDate
1	' Computer '	2008-12-29 16:25:46.635

We use the following SELECT statement:

SELECT EXTRACT (year from OrderDate) as Orderyear,extract (MONTH from OrderDate) as Ordermonth,extract (Day from OrderDate) A S Orderdayfrom Orderswhere orderid=1

Results:

OrderYear	OrderMonth	Orderday
2008	12	29

Date_add () Definition and usage

The Date_add () function adds a specified time interval to a date.

Grammar

Date_add (Date,interval expr type)

The date parameter is a valid day expression. The expr parameter is the time interval that you want to add.

The type parameter can be the following value:

Type value
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

Instance

Suppose we have the following table:

OrderId	ProductName	OrderDate
1	' Computer '	2008-12-29 16:25:46.635

Now, we want to add 2 days to "OrderDate" so we can find the payment date.

We use the following SELECT statement:

SELECT OrderId, as DATE_ADD(OrderDate,INTERVAL 2 DAY) orderpaydatefrom Orders

Results:

OrderId	orderpaydate
1	2008-12-31 16:25:46.635

Date_sub () Definition and usage

The Date_sub () function subtracts the specified time interval from the date.

Grammar

Date_sub (Date,interval expr type)

The date parameter is a valid day expression. The expr parameter is the time interval that you want to add.

The type parameter can be the following value:

Type value
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

Instance

Suppose we have the following table:

OrderId	ProductName	OrderDate
1	' Computer '	2008-12-29 16:25:46.635

Now, we want to subtract 2 days from "OrderDate".

We use the following SELECT statement:

SELECT OrderId, as DATE_SUB(OrderDate,INTERVAL 2 DAY) orderpaydatefrom Orders

Results:

OrderId	orderpaydate
1	2008-12-27 16:25:46.635

DATEDIFF () Definition and usage

The DATEDIFF () function returns the number of days between two dates.

Grammar

DATEDIFF (DATE1,DATE2)

The date1 and date2 parameters are valid date or date/time expressions.

Note: Only the date part of the value participates in the calculation.

Example 1

Use the following SELECT statement:

SELECT DATEDIFF (' 2008-12-30 ', ' 2008-12-29 ') as Diffdate

Results:

Diffdate
1

Example 2

Use the following SELECT statement:

SELECT DATEDIFF (' 2008-12-29 ', ' 2008-12-30 ') as Diffdate

Results:

Diffdate
-1

Date_format () Definition and usage

The Date_format () function is used to display date/time data in different formats.

Grammar

Date_format (Date,format)

The date parameter is a valid day. format Specifies the date/time in which the output is formatted.

The formats you can use are:

format	Description
%a	Abbreviated Week name
%b	Abbreviated month name
%c	Month, value
%d	Day of the month with English prefixes
%d	Day of the month, value (00-31)
%e	Day of the month, value (0-31)
%f	Microseconds
%H	Hours (00-23)
%h	Hours (01-12)
%I	Hours (01-12)
%i	Minutes, value (00-59)
%j	Days of the Year (001-366)
%k	Hours (0-23)
%l	Hours (1-12)
%M	Month Name
%m	month, value (00-12)
%p	AM or PM
%r	Time, 12-hour (Hh:mm:ss AM or PM)
%s	Seconds (00-59)
%s	Seconds (00-59)
%T	Time, 24-hour (HH:MM:SS)
%u	Week (00-53) Sunday is the first day of the week
%u	Week (00-53) Monday is the first day of the week
%V	Week (01-53) Sunday is the first day of the week, with%x
%v	Week (01-53) Monday is the first day of the week, with%x
%W	Week name
%w	Day of the week (0= Sunday, 6 = Saturday)
%x	year, of which Sunday was the first day of the week, 4-bit, with%V used
%x	year, of which Monday was the first day of the week, 4-bit, with%v used
%Y	Year, 4 guests
%y	Year, 2 guests

Instance

The following script uses the Date_format () function to display different formats. We use now () to get the current date/time:

Date_format (now (), '%b%d%Y%h:%i%p ') Date_format (now (), '%m-%d-%y ') Date_format (now (), '%d%b%Y ') Date_format (now (), '% D%b%Y%t:%f ')

The results are similar:

Dec 11:45 pm12-29-200829 Dec 0829 Dec 2008 16:25:46.635

MySQL Query SQL code for this week, last week, this month, and last month's data

querying data for the current week
SELECT Name,submittime from the Enterprise WHERE Yearweek (Date_format (submittime, '%y-%m-%d ')) = Yearweek (now ());

query last week's data
SELECT Name,submittime from the Enterprise WHERE Yearweek (Date_format (submittime, '%y-%m-%d ')) = Yearweek (now ())-1;

querying data for the current month
Select Name,submittime from Enterprise where Date_format (Submittime, '%y-%m ') =date_format (now (), '%y-%m ')

query data that is currently 6 months from now
Select Name,submittime from Enterprise where Submittime between Date_sub (now (), Interval 6 month) and now ();

querying data for the previous month
Select Name,submittime from Enterprise where Date_format (Submittime, '%y-%m ') =date_format (Date_sub (Curdate (), INTERVAL 1 MONTH), '%y-%m ')

SELECT * from ' user ' where Date_format (pudate, '%y%m ') = Date_format (Curdate (), '%y%m ');

SELECT * from user where WeekOfYear (from_unixtime (pudate, '%y-%m-%d ')) = WeekOfYear (now ())

SELECT *
From user
Where month (From_unixtime (pudate, '%y-%m-%d ')) = month (now ())

SELECT *
from [user]
Where year (From_unixtime (pudate, '%y-%m-%d ')) = year (now ())
And month (From_unixtime (pudate, '%y-%m-%d ')) = month (now ())

SELECT *
from [user]
Where pudate between the last day of last month
And the first day of next month

MySQL Date function