SQL Date Functions
Back to home
- What do you mean by SQL Date Functions?
- List of the functions
Table of Content
Logicmojo - Updated Aug 28, 2021
What do you mean by SQL Date Functions?
For newcomers to SQL, dates are difficult since, while working with databases, the format of the date in the table must match the input date in order to insert.
Datetime (time is also linked with date) is used in many contexts instead of date.
When working with dates, the most challenging element is ensuring that the format of the date you're trying to insert matches the format of the database's date column.
Your searches will operate as expected as long as your data just contains the date part.
When there is a temporal component, though, things become more complicated.
The table below is a collection of all the important Date and Time functions available in SQL.
Your RDBMS can also do a variety of additional tasks.
The list below is based on the MySQL RDBMS.
List of Date Functions
| Function | Description |
|---|---|
| ADDDATE() | Dates are added. |
| ADDTIME() | Adds time to the equation |
| CONVERT_TZ() | Returns the current date after converting from one timezone to another. |
| CURDATE() | CURDATE() Synonyms CURTIME() Synonyms |
| CURRENT_DATE() | Synonyms for the word "now" () |
| CURRENT_TIME() | The current time is returned. |
| CURRENT_TIMESTAMP() | The current time stamp is returned. |
| CURTIME() | Formats the date in the manner given. |
| DATE_ADD() | Two dates are added. |
| DATE_FORMAT() | DAYOFMONTH is a synonym for DAYOFMONTH () |
| DATE_SUB() | The name of the weekday is returned. |
| DATEDIFF() | Two dates are subtracted. |
| DAY() | Adds time to the equation |
| DAYNAME() | Returns the current date after converting from one timezone to another. |
| DAYOFMONTH() | The month's day is returned (1-31) |
| DAYOFWEEK() | The argument's weekday index is returned. |
| DAYOFYEAR() | Returns the current year's date (1-366) |
| EXTRACT() | The current time stamp is returned. |
| FROM_DAYS() | Takes a portion of a date and extracts it. |
| FROM_UNIXTIME() | Date is formatted as a UNIX timestamp. |
| HOUR() | The hour is extracted. |
| LAST_DAY() | The argument's last day of the month is returned. |
| LOCALTIME() | NOW is a synonym for () |
| MAKEDATE()() | Creates a date based on the year and day. |
| MICROSECOND() | Returns the argument's microseconds. |
| MINUTE() | The argument's minute is returned. |
| MONTH() | Formats the date in the manner given. |
| MONTHNAME() | Return the month as of the last day of the month. |
| NOW() | The month's name is returned. The current date and time are returned. |
| PERIOD_ADD() | Adds a month to a year-month calendar. |
| PERIOD_DIFF() | The number of months between periods is returned |
| QUARTER() | The quarter is returned from a date argument. |
| SEC_TO_TIME() | Converts seconds to the format 'HH:MM:SS'. |
| SECOND() | The second is returned (0-59) |
| SUBDATE() | SUB when called with three arguments () subtracts the number of times |
| SUBTIME() | The current time stamp is returned. |
| SYSDATE() | The function's execution time is returned. |
| TIME_FORMAT() | Formats in terms of time |
| TIME_TO_SEC() | Converts the argument to seconds and returns it. |
| TIME() | The time component of the expression passed is extracted. |
| TIMEDIFF() | 'Time is subtracted' |
Let's discuss all the DATE_FORMATS() in brief,
| Format | Description |
|---|---|
| %a | Abbreviated as weekday name (Sun-Sat) |
| %b | Abbreviated as month name (Jan-Dec) |
| %c | Represents Month, numeric (0-12). |
| %D | Represents Day of month with English suffix (0th, 1st, 2nd, 3rd) |
| %d | Represents Day of month, numeric (00-31) |
| %e | Represents Day of month, numeric (0-31) |
| %F | Represents Microseconds (000000-999999) |
| %H | Represents Hour (00-23) |
| %h | Represents Hour (01-12) |
| %I | Represents Hour (01-12) |
| %i | Represents Minutes, numeric (00-59) |
| %j | Represents Day of year (001-366) |
| %l | Abbreviated as Hour (1-12) |
| %M | Abbreviated as month name (Jan-Dec) |
| %m | Represents Month, numeric (00-12) |
| %p | Represents AM or PM |
| %r | Represents Time, 12-hour (hh:mm:ss followed by AM or PM) |
| %S | Represents Seconds (00-59) |
| %s | Represents Seconds (00-59) |
| %T | Represents Time, 24-hour (hh:mm:ss) |
| %U | Represents Week (00-53) where Sunday is the first day of week |
| %u | Represents Week (00-53) where Monday is the first day of week |
| %V | Represents Week (01-53) where Sunday is the first day of week, used with %X |
| %v | Represents Week (01-53) where Monday is the first day of week, used with %x |
| %W | Represents Microseconds (000000-999999) |
| %w | Represents Hour (00-23) |
| %X | Year for the week where Monday is the first day of week, four digits, used with %V |
| %x | Year for the week where Monday is the first day of week, four digits, used with %v |
| %Y | Represents Year, numeric, four digits |
| %y | Represents Year, numeric, two digits |
With this article at Logicmojo, you must have the complete idea of SQL Date Functions.