21 - SQL DATE functions

The DATE and TIME values are represented using DATE, TIME, DATETIME, TIMESTAMP and YEAR data type.  Each type has a range of valid values including the ZERO value.The TIMESTAMP has special auto update behavior.

The following table shows the format of the zero value for each type.

Data Type

“Zero” Value

DATE

'0000-00-00'

TIME

'00:00:00'

DATETIME

'0000-00-00 00:00:00'

TIMESTAMP

'0000-00-00 00:00:00'

YEAR

0000

There are different DATE and TIME functions that can be used to manipulate  temporal values.

Name

Description

ADDDATE()

Add time values (intervals) to a date value

ADDTIME()

Add time

CONVERT_TZ()

Convert from one timezone to another

CURDATE()

Return the current date

CURRENT_DATE(),CURRENT_DATE

Synonyms for CURDATE()

CURRENT_TIME(),CURRENT_TIME

Synonyms for CURTIME()

CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP

Synonyms for NOW()

CURTIME()

Return the current time

DATE_ADD()

Add time values (intervals) to a date value

DATE_FORMAT()

Format date as specified

DATE_SUB()

Subtract a time value (interval) from a date

DATE()

Extract the date part of a date or datetime expression

DATEDIFF()

Subtract two dates

DAY()

Synonym for DAYOFMONTH()

DAYNAME()

Return the name of the weekday

DAYOFMONTH()

Return the day of the month (0-31)

DAYOFWEEK()

Return the weekday index of the argument

DAYOFYEAR()

Return the day of the year (1-366)

EXTRACT()

Extract part of a date

FROM_DAYS()

Convert a day number to a date

FROM_UNIXTIME()

Format UNIX timestamp as a date

GET_FORMAT()

Return a date format string

HOUR()

Extract the hour

LAST_DAY

Return the last day of the month for the argument

LOCALTIME(),LOCALTIME

Synonym for NOW()

LOCALTIMESTAMP,LOCALTIMESTAMP()

Synonym for NOW()

MAKEDATE()

Create a date from the year and day of year

MAKETIME()

Create time from hour, minute, second

MICROSECOND()

Return the microseconds from argument

MINUTE()

Return the minute from the argument

MONTH()

Return the month from the date passed

MONTHNAME()

Return the name of the month

NOW()

Return the current date and time

PERIOD_ADD()

Add a period to a year-month

PERIOD_DIFF()

Return the number of months between periods

QUARTER()

Return the quarter from a date argument

SEC_TO_TIME()

Converts seconds to 'HH:MM:SS' format

SECOND()

Return the second (0-59)

STR_TO_DATE()

Convert a string to a date

SUBDATE()

Synonym for DATE_SUB() when invoked with three arguments

SUBTIME()

Subtract times

SYSDATE()

Return the time at which the function executes

TIME_FORMAT()

Format as time

TIME_TO_SEC()

Return the argument converted to seconds

TIME()

Extract the time portion of the expression passed

TIMEDIFF()

Subtract time

TIMESTAMP()

With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments

TIMESTAMPADD()

Add an interval to a datetime expression

TIMESTAMPDIFF()

Subtract an interval from a datetime expression

TO_DAYS()

Return the date argument converted to days

TO_SECONDS()

Return the date or datetime argument converted to seconds since Year 0

UNIX_TIMESTAMP()

Return a UNIX timestamp

UTC_DATE()

Return the current UTC date

UTC_TIME()

Return the current UTC time

UTC_TIMESTAMP()

Return the current UTC date and time

WEEK()

Return the week number

WEEKDAY()

Return the weekday index

WEEKOFYEAR()

Return the calendar week of the date (0-53)

YEAR()

Return the year

YEARWEEK()

Return the year and week

 

We will look at some of the functions which have been mentioned in above table:

1. DATE(expr):

Example:

mysql> SELECT DATE('2014-02-10');
+---------------------------+
| DATE('2014-02-10')        |
+---------------------------+
| 2014-02-10                |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE('2014-02-10 01:20-02');
+-----------------------------------------------+
| DATE('2014-02-10 01:20-02')                   |
+-----------------------------------------------+
| 2014-02-10                                    |
+-----------------------------------------------+
1 row in set (0.00 sec)

2. DATEDIFF(Expr1, expr2):

Gives the difference between the date parts in days.

mysql> SELECT DATEDIFF('2000-10-02','2000-09-01');
+--------------------------------------------------------+
| DATEDIFF('2000-10-02','2000-09-01')                    |
+--------------------------------------------------------+
|                                  31                    |
+--------------------------------------------------------+
1 row in set (0.03 sec)

mysql> SELECT DATEDIFF('2000-10-02','2000-11-01');
+--------------------------------------------------------+
| DATEDIFF('2000-10-02','2000-11-01')                    |
+--------------------------------------------------------+
|                                 -30                    |
+--------------------------------------------------------+
1 row in set (0.00 sec)

3. DAYNAME(Date)

This function returns the day of the month for the date. And will return number between 1 and 31.

 

mysql> SELECT DAYOFMONTH('2002-02-29');
+----------------------------------------------+
| DAYOFMONTH('2002-02-29')     |
+----------------------------------------------+
|                     NULL     |
+----------------------------------------------+
1 row in set, 1 warning (0.06 sec)

mysql> SELECT DAYOFMONTH('2002-02-28');
+----------------------------------------------+
| DAYOFMONTH('2002-02-28')     |    
+-----------------------------------------------+
|                       28     |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFMONTH('2004-02-29');
+----------------------------------------------+
| DAYOFMONTH('2004-02-29')     |
+----------------------------------------------+
|                       29     |
+----------------------------------------------+
1 row in set (0.00 sec)

In the first example, as 2002-02-29 was not a leap year, the output was given as NULL.

In the third example, 2004-02-29 being a leap year, 29 day was displayed.

Similar to DAYOFMONTH(date), there is DAYOFWEEK(date) which gives output in range of 1 to 7 and DAYSOFYEAR(date) which gives output in range of 1 to 366.

4. DATE_FORMAT(date,format)

This function will format the date value according to the format specified. The formats can be specified as below. % should be preceeding the character.

Specifier

Description

%a

Abbreviated weekday name (Sun..Sat)

%b

Abbreviated month name (Jan..Dec)

%c

Month, numeric (0..12)

%D

Day of the month with English suffix (0th, 1st, 2nd, 3rd, .)

%d

Day of the month, numeric (00..31)

%e

Day of the month, numeric (0..31)

%f

Microseconds (000000..999999)

%H

Hour (00..23)

%h

Hour (01..12)

%I

Hour (01..12)

%i

Minutes, numeric (00..59)

%j

Day of year (001..366)

%k

Hour (0..23)

%l

Hour (1..12)

%M

Month name (January..December)

%m

Month, numeric (00..12)

%p

AM or PM

%r

Time, 12-hour (hh:mm:ss followed by AM or PM)

%S

Seconds (00..59)

%s

Seconds (00..59)

%T

Time, 24-hour (hh:mm:ss)

%U

Week (00..53), where Sunday is the first day of the week

%u

Week (00..53), where Monday is the first day of the week

%V

Week (01..53), where Sunday is the first day of the week; used with %X

%v

Week (01..53), where Monday is the first day of the week; used with %x

%W

Weekday name (Sunday..Saturday)

%w

Day of the week (0=Sunday..6=Saturday)

%X

Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V

%x

Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v

%Y

Year, numeric, four digits

%y

Year, numeric (two digits)

%%

A literal .%. character

%x

x, for any.x. not listed above

mysql> SELECT DATE_FORMAT('2010-07-23','%Y %b %d');
+------------------------------------------------------------------+
| DATE_FORMAT('2010-07-23','%Y %b %d')     |
+------------------------------------------------------------------+
| 2010 Jul 23                              |
+------------------------------------------------------------------+
1 row in set (0.05 sec)

5. DATE_ADD(date, INTERVAL units) and DATE_SUB(date, INTERVAL units)

These functions perform the date arithmetic that is addition and subtraction of dates by the interval mentioned. The interval can be of days, months, years, seconds etc. The intervals that can be given are as below:

unit Value

ExpectedexprFormat

MICROSECOND

MICROSECONDS

SECOND

SECONDS

MINUTE

MINUTES

HOUR

HOURS

DAY

DAYS

WEEK

WEEKS

MONTH

MONTHS

QUARTER

QUARTERS

YEAR

YEARS

SECOND_MICROSECOND

'SECONDS.MICROSECONDS'

MINUTE_MICROSECOND

'MINUTES.MICROSECONDS'

MINUTE_SECOND

'MINUTES:SECONDS'

HOUR_MICROSECOND

'HOURS.MICROSECONDS'

HOUR_SECOND

'HOURS:MINUTES:SECONDS'

HOUR_MINUTE

'HOURS:MINUTES'

DAY_MICROSECOND

'DAYS.MICROSECONDS'

DAY_SECOND

'DAYS HOURS:MINUTES:SECONDS'

DAY_MINUTE

'DAYS HOURS:MINUTES'

DAY_HOUR

'DAYS HOURS'

YEAR_MONTH

'YEARS-MONTHS'

 

 

mysql> SELECT DATE_ADD('2001-01-01',INTERVAL 1 DAY);
+------------------------------------------------------------------+
| DATE_ADD('2001-01-01',INTERVAL 1 DAY)    |
+------------------------------------------------------------------+
| 2001-01-02                               |
+------------------------------------------------------------------+
1 row in set (0.02 sec)
Here the date was advanced by 1 day.
mysql> SELECT DATE_SUB('2001-01-01',INTERVAL 1 MONTH);
+------------------------------------------------------------------+
| DATE_SUB('2001-01-01',INTERVAL 1 MONTH)     |
+------------------------------------------------------------------+
| 2000-12-01                                  |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
Here the month was subtracted by 1.
mysql> SELECT DATE_ADD('2001-03-03 21:23:01', INTERVAL '5:4' MINUTE_SECOND);
+---------------------------------------------------------------------------------------------------------+
| DATE_ADD('2001-03-03 21:23:01', INTERVAL '5:4' MINUTE_SECOND)     |
+---------------------------------------------------------------------------------------------------------+
| 2001-03-03 21:28:05                                               |
+---------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

Like us on Facebook