sql date functions part 1

By | January 9, 2020

Some of the sql date functions have been discussed in the previous article. In this tutorial you can check more date functions.

1. ADDDATE(): It adds time to a date.

Output: 2010-05-25

2. ADDTIME(): It returns a date/time after adding certian interval of time.

Output: 2010-05-15 10:10:20

3. CONVERT_TZ(): It converts a datetime from one timezone to another timezone.

Output: 2010-05-15 21:00:00

4. CURDATE(): It returns the current date in ‘YYYY-MM-DD’ format or ‘YYYYMMDD’ format. CURRENT_DATE and CURRENT_DATE() are the synonyms of CURDATE().

Output: 2020-01-09

5. CURRENT_DATE(): It returns the current date in ‘YYYY-MM-DD’ format or YYYYMMDD format. CURDATE() and CURRENT_DATE() are the synonyms of CURRENT_DATE.

Output: 2020-01-09

6. CURRENT_TIME(): It returns the current time as a value in ‘hh:mm:ss’ or hhmmss format, depending on whether the function is used in string or numeric context.
CURRENT_TIME() and CURRENT_TIME are the synonyms of CURTIME.

Output: 02:22:17

7. CURRENT_TIMESTAMP(): It returns the current date and time in ‘YYYY-MM-DD HH:MM:SS’ format or YYYYMMDDHHMMSS.uuuuuu format.
NOW() and CURRENT_TIMESTAMP() are the synonyms of CURRENT_TIMESTAMP().

Output: 2020-01-09 02:56:08

8. CURTIME(): It returns the current time as a value in ‘hh:mm:ss’ or hhmmss format, depending on whether the function is used in string or numeric context.
CURRENT_TIME() and CURRENT_TIME are the synonyms of CURTIME().

Output: 02:22:17

9. DATE(): It extracts the DATE part from datetime expression.

Output: 2010-10-15

10. DATE_ADD(): It adds time values/intervals to a date value.
Syntax: DATE_ADD(date,INTERVAL expr unit);

Output: 2020-01-31

11. DATE_FORMAT(): It formats the date as specified.

Output: Monday July 2020
%W: Weekday name (Sunday..Saturday)
%M: Month name (January..December)
%Y: Year, numeric, four digits

12. DATE_SUB(): Subtract a time value from a date.

Output: 2020-07-20 10:10:09

13. DATEDIFF(): It substracts two given dates. It returns expr1 − expr2 as a value in days from one date to the other. Only the date parts of the values are used in the calculation.

Output: 1

14. DAY(): It returns the day from a specified date. Returned date will be within the range of 1 to 31. The DAYOFMONTH() is synonym of DAY().

Output: 15

15. DAYNAME(): It returns the name of the day from the given date. DAYOFMONTH() is the synonym of DAY().

Output: Friday

16. DAYOFMONTH(): It returns the name of the day from the given date. DAY() is the synonym of DAYOFMONTH().

Output: 15

17. DAYWEEK(): It returns the week day number for example, 1 for Sunday,…… 7 for Saturday ) for the specified date.

Output: 6

18. DAYOFYEAR(): It returns the day of the year for the given date. The range of return value is within 1 to 366. (366 is due to an extra day added in leap year)

Output: 288

Output: 366

19. EXTRACT(): It extracts part of a date.

Output: 2035

20. FROM_DAYS(): It extratcs the date from the given value.

Output: 2011-04-08

21. HOUR(): It extracts the hour value from the given date/time.

Output: 14

Output: 20

22. LAST_DAY(): It returns the last day of the corresponding month for a date or datetime value. It returns NULL if the date or datetime value is invalid.

Output: 2020-02-29

23. LOCALTIME(): It returns the value of current date and time in ‘YYYY-MM-DD HH:MM:SS’ format or YYYYMMDDHHMMSS format depending on the context.

Output: 2020-01-09 09:42:53

24. LOCALTIMESTAMP(): It returns the value of current date and time in ‘YYYY-MM-DD HH:MM:SS’ format or YYYYMMDDHHMMSS format depending on the context.

Output: 2020-01-09 09:44:12
Note: NOW, NOW(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), LOCALTIME, LOALTIME(), LOCALTIMESTAMP are synonyms of LOCALTIMESTAMP().

25. MAKEDATE(): It returns a date, given year and day-of-year values. dayofyear must be greater than 0 or the result is NULL.

Output: 2015-02-01, 2009-03-01, NULL

26. MAKETIME(): It returns a time value calculated from the hour, minute, and second arguments. The value of hour may be greater than 24 but the values of MINUTE and SECONDS
should not be greater than 59 because it returns NULL.

Output: 12:11:30

Output: NULL

28:45:59

 

This article has been contributed by Swasthik. If you like selenium99.com and would like to contribute any articles, please mail us to techygeeks99@gmail.com. 

If you find any correction in the article, please mention in the comments section.

 

Recommended Posts:

SQL Date Functions

SQL quick reference for interview preparation

Java program to split string

Maps in Java

Java interview questions

Hashset and LinkedHashset differences in java

Common exceptions in Selenium Webdriver

Advanced SQL Functions

 

Leave a Reply

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