In SQL, there are a lot of library functions used while working with Date in Databases. The given below are the list of SQL date functions along with examples.
SQL Date Functions
- NOW():
NOW() function returns the current date and time of the system.
Example:
Query: Select NOW ();
Result: 2018-14-21 15:08:05
- CURDATE():
CURDATE() functions return current date of the system.
Example:
Query: Select CURDATE ();
Result: 2018-14-21
- CURTIME():
CURTIME() function returns current time of the system
Example:
Query: Select CURTIME ();
Result: 15:17:19
- DATE():
DATE() function accepts string having date & time and gets date alone from it.
Example:
Query: Select DATE (‘1996-09-26 16:44:15.581’);
Result: 1996-09-26
- EXTRACT():
EXTRACT() is used to get single part like DAY, HOUR, MONTH, WEEK, QUARTER, YEAR, SECOND etc.
Syntax: SELECT EXTRACT(unit FROM date);
Example:
Query: Select EXTRACT (DAY from ‘1996-09-26 16:44:15.581’);
Result: 26
Query: Select EXTRACT (HOUR from ‘1996-09-26 16:44:15.581’);
Result: 16
- DATE_ADD():
DATE_ADD() add specified time interval to given date
Syntax: SELECT DATE_ADD(date, INTERVAL expression type)
Example:
Query: Select DATE_ADD (‘1996-09-26 16:44:15.581’, INTERVAL 1 YEAR);
Result: 1997-09-26 16:44:15.581
Query: Select DATE_ADD (‘1996-09-26 16:44:15.581’, INTERVAL 1 HOUR);
Result: 1996-09-26 17:44:15.581
- DATE_SUB:
DATE_SUB() subtracts specified time interval from given date
Syntax: SELECT DATE_SUB(date, INTERVAL expression type)
Example:
Query: Select DATE_SUB (‘1996-09-26 16:44:15.581’, INTERVAL 1 YEAR);
Result: 1995-09-26 16:44:15.581
Query: Select DATE_SUB (‘1996-09-26 16:44:15.581’, INTERVAL 1 HOUR);
Result: 1996-09-26 15:44:15.581
- DATEDIFF():
DATEDIFF() gives difference between two dates in number of days.
Syntax: SELECT DATEDIFF(date1, date2);
Example:
Query: Select DATEDIFF (‘1998-09-26 16:44:15.581’, ‘1997-09-26 16:44:15.581’);
Result: 365
- DATE_FORMAT():
DATE_FORMAT returns date in given format
Syntax: SELECT(date, format)
The format can be any one of the following:
- %a- Weekday name in abbreviated form (Sun-Sat)
- %b- Month name in abbreviated from (Jan-Dec)
- %c-Month in number (0-12)
- %D-Day of month with suffix like th,rd etc (0th, 1st, 2nd, 3rd)
- %d-Day of month in numeric (00-31)
- %e-Day of month in numeric (0-31)
- %f-Include Microseconds (000000-999999)
- %H-Hour in 24 format(00-23)
- %h-Hour in 12 format (01-12)
- %I-Hour in 12 format (01-12)
- %i-Minutes in numbers (00-59)
- %j-Day of year in 3 digit form (001-366)
- %k-Hour in24 format(0-23)
- %l-Hour in 24 format(1-12)
- %M-Full Month name (January-December)
- %m-Month in numeric form(00-12)
- %p-Mention AM or PM
- %r-Time in 12-hour (hh:mm:ss followed by AM or PM)
- %S-Display Seconds (00-59)
- %s-Display Seconds (00-59)
- %T-Time in 24-hour (hh:mm:ss)
- %U-Week number(00-53) where Sunday is the first day of week
- %u-Week number(00-53) where Monday is the first day of week
- %V-Week number(01-53) where Sunday is the first day of week, used with %X
- %v-Week in numeric form (01-53) where Monday is the first day of 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 week, in four digits, when used with %V
- %x-Year for the week where Monday is the first day of week, in four digits, when used with %v
- %Y-Year in numeric, four digits
- %y-Year in numeric, two digits
Example:
Query: Select DATE_FORMAT(NOW(), ‘%d %b %y’);
Result: 21 Apr 18
This is all about the SQL Date Functions and their description. If you have any query or doubt regarding this please mention in below comments or mail us. Hope you like this article.