There is very little consistency in date and time functions across different DBMS vendors. Largely, this is because most of them developed date and time data types ahead of the development of standards. Because of this diversity, date and time functions are presented in summary form for Microsoft SQL Server, Oracle, DB2, and MySQL. As always, the vendor documentation should be consulted for detailed explanations of the use of these functions. Terms shown in italics are defined in the notes at the bottom of each table. The term “datetime” is used throughout this section to mean a character string that contains both a date and time in a format that is acceptable to the particular DBMS. Microsoft SQL Server Date and Time Functions Microsoft SQL Server offers the date and time functions shown in the following table: Function | Purpose | Input Parameters | DATEADD | Returns a new datetimecalculated by adding an interval to the datepart of the supplied date | datepart, interval quantity, datetime | DATEDIFF | Returns the number of datetime boundaries crossed between two dates | datepart, start datetime, end datetime | DATENAME | Returns a text name representing the selected datepartof the input datetime | datepart, datetime |
Function | Purpose | Input Parameters | DATEPART | Returns an integer representing the selected datepartof the supplied datetime | datepart, datetime | DAY | Returns an integer representing the day contained in the supplied datetime | datetime | GETDATE | Returns the current system datetime | None | GETUTCDATE | Returns the current UTC (Universal Coordinated Time) datetime | None | MONTH | Returns an integer representing the month contained in the supplied datetime | datetime | YEAR | Returns an integer (four digits) representing the year contained in the supplied datetime | datetime |
NOTE: Datepart is a parameter that specifies a part of a date, such as year, month, day, hour, minute, second, and millisecond. Refer to Microsoft SQL Server documentation for values and options.
Oracle Date and Time Functions Oracle has more than 24 date and time functions. Remember that while Oracle calls the data type DATE, all dates contain a time component—it’s just set to zeros (representing midnight) when it’s not used. The functions you are most likely to use are listed in the following table: Function | Purpose | Input Parameters | ADD_MONTHS | Adds the supplied number of months to the supplied date | date, number of months (positive or negative value) | CURRENT_DATE | Returns the current date in the time zone set for the database session | None | EXTRACT | Extracts the specified datetime field from the supplied date | datetime field keyword, date | LAST_DAY | Returns the supplied date with the day shifted to the last day of the month | date |
Function | Purpose | Input Parameters | MONTHS_BETWEEN | Returns the number of months (including fractional parts) between the two supplied dates; result is negative if second date is before the first date | first date, second date | SYSDATE | Returns the current system date and time | None | TO_CHAR | When used with a date, converts the date to a character string in a format specified by the format string | date, format_string | TO_DATE | Converts the supplied character string into an Oracle internally formatted date, using the format string as a template for interpreting the character string’s contents | date, format_string | TRUNC | Truncates a date to the time unit specified in the datetime field keyword. If the keyword is omitted, the date is truncated to the current day | date, datetime field keyword |
NOTE: - Datetime field keyword is a keyword that specifies one of the fields contained within an Oracle date, such as YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
- Format string is a character string of symbols that specify the format that is to be used for the date when converted to or from a character string. There are over 40 different symbols that may be used in the format string (see Oracle documentation for an exhaustive list). For example, the format string ‘MM/DD/YYYY HH:MI’ would refer to a date character string that would look like ‘12/01/2004 11:58’, while the format string ‘DD-MON-RR’ (the Oracle default format) would refer to a string that would look like ‘01-Dec-04’.
- TO_CHAR can also be used to convert numeric values to character strings.
- TRUNC can also be used to truncate numeric values, which chops off any numbers to the right of the decimal point.
MySQL Date and Time Functions MySQL has well over 30 date and time functions. Of those, the ones you are most likely to use are listed in the following table: Function | Purpose | Input Parameters | ADDDATE | Adds two date, interval, or datetime expressions, yielding a new date | expression 1, expression 2 | ADDTIME | Adds two time expressions, yielding a new time | expression 1, expression 2 | CURDATE | Returns the current date in YYYY-MM-DD format | None | DATE | Returns the date part of a date or datetime expression | datetime expression | DATEDIFF | Returns the number of days between two dates | start date, end date | DATE_FORMAT | Formats a date according to a format string | date, format string | DAYNAME | Returns the text name for the day of the week contained in a date | date | DAYOFMONTH | Returns the day of the month, in the range 1 to 31 | date | DAYOFWEEK | Returns a weekday index number for the day contained in a date (1 for Sunday, 2 for Monday, and so forth) | date | DAYOFYEAR | Returns the day of the year for the day contained in a date with a valid range of 1 to 366 | date | LAST_DAY | Changes the day in a date to the last day of the month | date | MONTH | Returns the month contained in a date with a valid range of 1 to 12 | date | MONTHNAME | Returns the text name of the month contained in a date | date | NOW | Returns the current date and time | None | STR_TO_DATE | Converts a character string to a datetime format data item; the format stringindicates the format of the date information in the input character string | character string, format string | TIME | Extracts the time part of a datetime or time expression | datetime |
Function | Purpose | Input Parameters | TIMEDIFF | Returns the time difference between two datetime or time expression parameters | expression 1, expression 2 | TIME_FORMAT | Formats a time according to the format string | time, format string | UTC_DATE | Returns the current UTC (Universal Coordinated Time) date | None | UTC_TIME | Returns the current UTC (Universal Coordinated Time) time | None | WEEKOFYEAR | Returns the week of the year for a date, in the range 1 to 54 | date |
NOTE: Format string is a string of characters that indicates formatting options for parts of the date. Consult MySQL documentation for details.
DB2 Date and Time Functions DB2 UDB contains over 20 date and time functions. Of those, the ones you are most likely to use are listed in the table that follows: Function | Purpose | Input Parameters | DATE | Converts an expression into a date | expression | DAY | Returns the day part of a datetime expression | datetime expression | DAYNAME | Returns the text name of the day of the week for a date or datetime expression | datetime expression | DAYOFWEEK | Returns the day of the week (1 for Sunday, 2 for Monday, and so forth) for a datetime expression | datetime expression | DAYS | Returns an integer representation of a date | datetime expression | MINUTE | Returns the minute part of a datetime expression | datetime expression | MONTH | Returns the month part of a datetime expression | datetime expression | MONTHNAME | Returns the text name of the month for a date or datetime expression | datetime expression | QUARTER | Returns an integer in the range 1 to 4 representing the calendar quarter in which a date falls | datetime expression | SECOND | Returns the seconds part of a date or datetime expression | datetime expression |
Function | Purpose | Input Parameters | TIME | Returns the time part of a date or datetime expression | datetime expression | WEEK | Returns the week of the year as an integer in the range 1 to 54 | datetime expression | YEAR | Returns the year part of a date or datetime expression | datetime expression |
|
