Advanced Query Writing - Date and Time Functions

(Page 4 of 4 )

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

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Advanced Query Writing

· This article is an excerpt from the book "SQL DeMYSTified," published by...    · In response to:Null Value Function (NVL, ISNULL, IFNULL) ... there is no DB2...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter six of the book SQL DeMYSTiFied, written by Andrew Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249). Check it out today at your favorite bookstore. Buy this book now.