sql date functions

By | January 9, 2020

In this tutorial, we are going to learn different kinds of date functions used in sql. In this page I am focusing on the most commonly used functions.

1. now()
2. sysdate()
3. curdate()/current_date()
4. curtime()/current_time()
5. current_timestamp()
6. date()
7. date_add()
8. date_sub()
9. datediff()
10. extract()
11. date_format()

 

1. NOW(): Returns the current date and time as a value in ‘YYYY-MM-DD hh:mm:ss’ or YYYYMMDDhhmmss format, depending on whether the function is used in string or numeric context.

Output: ‘2020-01-08 04:23:38’

Output: 20200108042632
Note: CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW()

2. SYSDATE(): Returns the current date and time as a value in ‘YYYY-MM-DD hh:mm:ss’ or YYYYMMDDhhmmss format, depending on whether the function is used in string or numeric context.
Note: However NOW() and SYSDATE() have a small difference in their behaviour when executed in a stored function or in a single sql command. Please look at below examples.

Output: 2020-01-08 04:33:27, 0, 2020-01-08 04:33:27

Output: 2020-01-08 04:34:01, 0, 2020-01-08 04:34:04

3. CURDATE(): Returns the current date in ‘YYYY-MM-DD’ or YYYYMMDD formats, depending on whether the function is used in string or numeric context.

Output: 2020-01-08
Note: CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().

4. curtime(): Returns the current time in ‘hh:mm:ss’ or hhmmss formats, depending on whether the function is used in string or numeric context.

Output: 04:41:46

5. CURRENT_TIMESTAMP(): Returns the current date and time as a value in ‘YYYY-MM-DD hh:mm:ss’ or YYYYMMDDhhmmss formats. It is a synonym for NOW();

Output: 2020-01-08 04:42:41

6. DATE(): It extracts the date part of a date or datetime expression.

Output: 2020-01-09

7. DATE_ADD(): It adds time values/intervals to a date value.
Syntax: DATE_ADD(date,INTERVAL expr unit);
expr: It is an expression and it specifies the interval value to be added to the starting date. expr is evaluated as a string; it may start with a – for negative intervals.
unit: It is a keyword and it indicates the units in which the expression should be interpreted.

Output: 2020-01-31

Output: 2020-05-30

Output: 2021-04-30

Output: 2020-07-20 12:00:00

Output: 2020-07-20 11:31:21

MINUTE_SECOND unit adds time interval to both minutes and seconds as specified in the query. In the above query, it adds 1 minute to minute section and 1 second to seconds section so 30 min became 31 min and 20 sec became 21 sec.

Output: 2020-07-21 01:01:00

Output: 2020-07-21 01:00:00

Output: 2020-07-20 10:10:10.0009

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

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

Output: 2019-07-20 10:10:10

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

9. 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

Output: -1

Output: 30

Output: 1

10. EXTRACT(): Extract part of a date.

Output: 2020

Output: 7

Output: 20

Output: 190204

Output: 202007

Output: 25

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

Output: AM
%p: AM or PM

 

Q. What is the difference between curdate() and currentdate()?
Ans: There is no difference between these tow functions. CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().


This article has been contributed by Rithanya. If you like selenium99.com and would like to contribute any articles, please send your articles to techygeeks99@gmail.com

 

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 *