SQL Date Functions

By | October 2, 2021
SQL Date Functions

SQL provides a specific data type for date-related data; there may come many cases when we require some special functions for all those dates stored in the database. For example, if we want to show the current date and time, then we can use the date NOW() Function.

Different RDBMS have different date functions to perform a specific date operation, here in this article all the SQL date-related functions we have provided are from MYSQL. Some of the functions may not work on all the other RDBMS such as Oracle, SQL Server, PostgreSQL, etc.

Vamware

SQL Date Functions

  • ADDDATE()
  • ADDTIME()
  • CONVERT_TZ()
  • CURDATE()
  • CURRENT_DATE(), CURRENT_DATE
  • CURRENT_TIME(), CURRENT_TIME
  • CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
  • CURTIME()
  • DATE_FORMAT()
  • DATE()
  • DATEDIFF()
  • DAY()
  • DAYNAME()
  • DAYOFMONTH()
  • DAYOFWEEK()
  • DAYOFYEAR()
  • EXTRACT
  • FROM_DAYS()
  • FROM_UNIXTIME()
  • HOUR()
  • LAST_DAY
  • LOCALTIME(), LOCALTIME
  • LOCALTIMESTAMP, LOCALTIMESTAMP()
  • MAKEDATE()
  • MAKETIME
  • MICROSECOND()
  • MINUTE()
  • MONTH()
  • MONTHNAME()
  • NOW()
  • PERIOD_ADD()
  • PERIOD_DIFF()
  • QUARTER()
  • SEC_TO_TIME()
  • SECOND()
  • STR_TO_DATE()
  • SUBDATE()
  • SUBTIME()
  • SYSDATE()
  • TIME_FORMAT()
  • TIME_TO_SEC()
  • TIME()
  • TIMEDIFF()
  • TIMESTAMP()
  • TIMESTAMPADD()
  • TIMESTAMPDIFF()
  • TO_DAYS()
  • UNIX_TIMESTAMP(
  • UTC_DATE()
  • UTC_TIME()
  • UTC_TIMESTAMP()
  • WEEK()
  • WEEKDAY()
  • WEEKOFYEAR()
  • YEAR()
  • YEARWEEK() 

1. ADDDATE()

ADDDATE() Function is used to add interval days on a date. It accepts two values as parameters. The first parameter should be the date on which we are supposed to add Days. The second parameter should be the INTERVAL keyword with the number of unit values we want to add on the date.

Syntax

ADDDATE(date,INTERVAL unit Value);

Example

SELECT DATE_ADD('2020-03-03', INTERVAL 31 DAY);

Output

+-----------------------------------------+
| DATE_ADD('2020-03-03', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2020-04-03                              |
+-----------------------------------------+

1 row in set (0.05 sec)

Unit Values to add Different intervals:

  • 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

2. ADDTIME()

The Function of ADDTIME() is similar to ADDATE, but here instead of adding Dates, we add time.

Syntax:

ADDTIME(date&time, time to add)

Example:

SELECT ADDTIME('2020-01-31 23:59:59.999999','1 1:1:1.000002');

Output
+--------------------------------------------------------+
| ADDTIME('2020-01-31 23:59:59.999999','1 1:1:1.000002') |
+--------------------------------------------------------+
| 2020-02-02 01:01:01.000001                             |
+--------------------------------------------------------+ 

3. CONVERT_TZ()

This function is used to convert the time zone from one to another.

Syntax

CONVERT_TZ(date,from,to)

Example

SELECT CONVERT_TZ('2020-01-01 12:00:00','+00:00','+10:00');

Output
+-----------------------------------------------------+
| CONVERT_TZ('2020-01-01 12:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 2020-01-01 22:00:00                                 |
+-----------------------------------------------------+

4. CURDATE()

This function returns the current cate in “YYYY-MM-DD” format.

Example

SELECT CURDATE() as Today;

Output
+------------+
| Today     |
+------------+
| 2020-05-15 |
+------------+

5. CURRENT_DATE & CURRENT_DATE()

These two are similar to CURDATE().

Example

SELECT CURRENT_DATE;

Output
+--------------+
| CURRENT_DATE |
+--------------+
| 2020-05-15   |
+--------------+

6. CURTIME()

This function returns the current time in HH:MM: SS format.

SELECT CURTIME();

Output
+-----------+
| CURTIME() |
+-----------+
| 12:07:56  |
+-----------+

7. CURRENT_TIME & CURRENT_TIME()

These are similar to CURTIME() function

Example

SELECT CURRENT_TIME;

Output
+--------------+
| CURRENT_TIME |
+--------------+
| 12:08:49     |
+--------------+

8. DATE()

With the Date() function, we can extract the date from a DateTime expression.

Example

SELECT DATE('2019-11-02 02:03:04');

Output
+-----------------------------+
| DATE('2019-11-02 02:03:04') |
+-----------------------------+
| 2019-11-02                  |
+-----------------------------+

9. DATEDIFF()

This Function can return the number of days difference between two date or DateTime periods.

Example:

SELECT DATEDIFF('2010-01-31 23:59:59','2000-01-31');

Output
+----------------------------------------------+
| DATEDIFF('2010-01-31 23:59:59','2000-01-31') |
+----------------------------------------------+
|                                         3653 |
+----------------------------------------------+

10. DATE_FORMAT

This command is used to format a date and time into different string formats, so for a user, it becomes easy to read the date and time.

Syntax

 DATE_FORMAT(date,String_format)

Example

SELECT DATE_FORMAT('2020-01-01 22:23:00', '%W %M %Y');

Output
+------------------------------------------------+
| DATE_FORMAT('2020-01-01 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Wednesday January 2020                         |
+------------------------------------------------+

To format the date, we can use various string Specifiers such as:

String Specifiers Description
%a It specifies the abbreviate Weekday names such as Sun, Mon…Sat
%b It specifies the abbreviate Month names such as Jan, Feb…Dec
%c Numeric form for months 0 to 12
%D Specify the day of the month, such as 0th, 1st, 2nd, …. 31st.
%d Numeric representation of month days 0 to 31
%e Similar to %d
%f Specify microseconds 000000 to 999999
%H Specify hours from 00 to 23
%h Specify hours from 01 to 12
%I Similar to %h specifies hours from 01 to 12 with AM and PM domain
%i Show minutes in numeric 00 to 59
%j Show year days 001 to 366
%k Show hour from 0 to 23
%M Specify month name from January to December
%m Specify months in numeric from 00 to 12
%p Specify Time-domain AM and PM
%r specify Time in 12-hour interval with AM and PM time domain
%S Specify Seconds
%T Specify the time in 24-hour format
%U Specify week and treat Sunday as the first day of the week
%u Specify week and treat Monday as the first day of the week
%W Specify weekday name from Sunday to Saturday
%w Specify Day of the week as 0 = Sunday and 6 = Saturday
%Y Specify year in 4 digits numeric form such as 1999, 1990, 2020, etc.
%y Specify year in 2 digits numeric form such as 20, 99, 01, etc.

11. Day()

It returns the number of days from the date.

Example

SELECT DAY('2020-01-01');

Output
+-------------------+
| DAY('2020-01-01') |
+-------------------+
|                 1 |
+-------------------+

12. DAYNAME()

It returns the weekday name of the Date.

Example

SELECT DAYNAME('2020-01-01');

Output
+-----------------------+
| DAYNAME('2020-01-01') |
+-----------------------+
| Wednesday             |
+-----------------------+

13. DAYOFMONTH()

It returns the day of the month from the passed date.

Example

SELECT DAYOFMONTH('2020-01-01');

Output
+--------------------------+
| DAYOFMONTH('2020-01-01') |
+--------------------------+
|                        1 |
+--------------------------+

14. DAYOFWEEK()

This function returns the number of weeks from the date.

Example

SELECT DAYOFWEEK('2020-02-13');

Output
+-------------------------+
| DAYOFWEEK('2020-02-13') |
+-------------------------+
|                       5 |
+-------------------------+

15. DAYOFYEAR()

It is similar to the DAY() Function

Example

SELECT DAYOFYEAR('2020-02-13');

Output
+-------------------------+
| DAYOFYEAR('2020-02-13') |
+-------------------------+
|                      44 |
+-------------------------+

16. EXTRACT()

EXTRACT () function is used to extract a specific unit from a DateTime expression.

Syntax

EXTRACT(unit FROM date)

Example

SELECT EXTRACT(MONTH FROM '2020-01-09');

Output
+----------------------------------+
| EXTRACT(MONTH FROM '2020-01-09') |
+----------------------------------+
|                                1 |
+----------------------------------+

17. FROM_DAYS()

This Function considers the Gregorian calendar(1582) as a base and converts the number of days into a Date.

Example

SELECT FROM_DAYS(737669);

Output
+-------------------+
| FROM_DAYS(737669) |
+-------------------+
| 2019-09-02        |
+-------------------+

18.  FROM_UNIXTIME()

This Function accepts a numeric value which represents the number of seconds from the UNIX timestamp.

Example

SELECT FROM_UNIXTIME(1575996580);

Output
+---------------------------+
| FROM_UNIXTIME(1575996580) |
+---------------------------+
| 2019-12-10 22:19:40       |
+---------------------------+

19. HOUR()

This function returns the number of HOURS form the time expression.

Example

SELECT HOUR('20:10:04');

Output
+------------------+
| HOUR('20:10:04') |
+------------------+
|               20 |
+------------------+

20. LAST_DAY()

It returns the last date of the month from the date expression.

Example

SELECT LAST_DAY('2020-02-01');

Output
+------------------------+
| LAST_DAY('2020-02-01') |
+------------------------+
| 2020-02-29             |
+------------------------+

21. LOCALTIME & LOCALTIME()

These functions are similar to NOW() and return the current date and time.

Example

SELECT LOCALTIME;

Output
+---------------------+
| LOCALTIME           |
+---------------------+
| 2020-05-15 13:32:14 
+---------------------+

22. MAKEDATE()

This Function can create a date expression by mentioning the Year and number of days.

Syntax

MAKEDATE(year, day of the year)

Example

SELECT MAKEDATE(2020,40);

Output
+-------------------+
| MAKEDATE(2020,40) |
+-------------------+
| 2020-02-09        |
+-------------------+

23. MAKETIME()

This Function can create time expression by mentioning the Hour, minute and second arguments.

Example

SELECT MAKETIME(11,10,20);

Output
+--------------------+
| MAKETIME(11,10,20) |
+--------------------+
| 11:10:20           |
+--------------------+

24. MICROSECOND()

It can extract the Microseconds from the datetime expression.

Example

SELECT MICROSECOND('11:00:00.28282');

Output
+-------------------------------+
| MICROSECOND('11:00:00.28282') |
+-------------------------------+
|                        282820 |
+-------------------------------+

25. Minute()

It can extract minutes from a datetime expression.

Example

SELECT MINUTE('2019-05-04 11:03:01');

Output
+-------------------------------+
| MINUTE('2019-05-04 11:03:01') |
+-------------------------------+
|                             3 |
+-------------------------------+

26. Month()

It can extract a month from a Date Time expression.

Example

SELECT MONTH('2019-05-04 11:03:01');

Output
+------------------------------+
| MONTH('2019-05-04 11:03:01') |
+------------------------------+
|                            5 |
+------------------------------+

27. MONTHNAME()

It can return the Month name from the datetime expression

Example

SELECT MONTHNAME('2019-05-04 11:03:01');

Output
+----------------------------------+
| MONTHNAME('2019-05-04 11:03:01') |
+----------------------------------+
| May                              |
+----------------------------------+

28. PERIOD_ADD(Period, Num)

This Function adds the number of months Num to a period “Period”. Here the period will be in the form of YYYMM, and it returns the output in the form of YYYYMM.

Syntax

PERIOD_ADD(YYYYMM, Month)

Example

SELECT PERIOD_ADD(202001,2);

Output
+----------------------+
| PERIOD_ADD(202001,2) |
+----------------------+
|               202003 |
+----------------------+

29. PERIOD_DIFF()

This function returns the difference between the two periods.

Syntax

PERIOD_DIFF(YYYYMM, YYYYMM)

Example

SELECT PERIOD_DIFF(202003,202001);

Output
+----------------------------+
| PERIOD_DIFF(202003,202001) |
+----------------------------+
|                          2 |
+----------------------------+

30. SECOND()

It can extract the Seconds from a DateTime expression.

Example

SELECT SECOND('2019-05-04 11:03:01');

Output
+-------------------------------+
| SECOND('2019-05-04 11:03:01') |
+-------------------------------+
|                             1 |
+-------------------------------+

31. STR_TO_DATE()

This function accepts a string and converts it into a DateTime expression using the DateTIme specifiers.

Syntax

STR_TO_DATE(Date_str,format)

Example

SELECT STR_TO_DATE('01/31/2020', '%m/%d/%Y');

Output
+---------------------------------------+
| STR_TO_DATE('01/31/2020', '%m/%d/%Y') |
+---------------------------------------+
| 2020-01-31                            |
+---------------------------------------+

32. SYSDATE()

Similar to NOW() Function it returns the current date present in the system.

Example

SELECT SYSDATE();

Output
+---------------------+
| SYSDATE()           |
+---------------------+
| 2020-05-15 13:52:44 |
+---------------------+

33. TIMEDIFF()

It returns the time difference between two datetime expression.

Example

SELECT TIMEDIFF('2020-12-31 23:59:59','2019-12-30 23:59:59');

Output
+-----------------------------------------------------------+
| TIMEDIFF('2020-12-31 23:59:59',    '2019-12-30 23:59:59') |
+-----------------------------------------------------------+
| 838:59:59                                                 |
+-----------------------------------------------------------+

34. TIMESTAMP()

It returns the UNIX timestamp of the datetime expression.

Example

SELECT TIMESTAMP('2020-01-01');

Output
+-------------------------+
| TIMESTAMP('2020-01-01') |
+-------------------------+
| 2020-01-01 00:00:00     |
+-------------------------+

35. Time_FORMAT()

It is similar to DATE_FORMAT() Function for Time.

Example

SELECT TIME_FORMAT('23:05:00', '%r');

Output
+-------------------------------+
| TIME_FORMAT('23:05:00', '%r') |
+-------------------------------+
| 11:05:00 PM                   |
+-------------------------------+

36. TIME_TO_SEC()

This Function can convert a give time expression to the number of seconds.

Example

SELECT TIME_TO_SEC('12:00:00');

Output
+-------------------------+
| TIME_TO_SEC('12:00:00') |
+-------------------------+
|                   43200 |
+-------------------------+

37. UTC_DATE()

This function returns the current UTC date.

Example

SELECT UTC_DATE();

Output
+------------+
| UTC_DATE() |
+------------+
| 2020-05-15 |
+------------+

38. WEEKDAY()

This Function returns a weekday from a date expression, and the weekday range from 0 to 6, where 0 = Monday and 6 = Sunday.

Example

SELECT WEEKDAY('2020-11-04');

Output
+-----------------------+
| WEEKDAY('2020-11-04') |
+-----------------------+
|                     2 |
+-----------------------+

39. YEAR()

This Function can extract Year from a DateTime expression.

Example

SELECT YEAR('2020-11-04');

Output
+--------------------+
| YEAR('2020-11-04') |
+--------------------+
|               2020 |
+--------------------+

40. YEARWEEK():

It returns the Year and week from a date expression in YYYYMM format.

Example

SELECT YEARWEEK('2020-11-04');

Output
+------------------------+
| YEARWEEK('2020-11-04') |
+------------------------+
|                 202044 |
+------------------------+

Summary

  • SQL has a specific data type for Date and Time data.
  • However, date and time mentioned inside single and double inverted comma like a string, still they are different from the string.
  • In SQL, we have different date time functions to perform operations on Date time expression.
  • Most of the datetime functions are similar and perform the same operation.
  • All the functions we have mentioned work fine of MYSQL.

People are also reading:

Leave a Reply

Your email address will not be published. Required fields are marked *