Home arrow MySQL arrow Page 4 - Advanced Query Writing

Date and Time Functions - MySQL

This article, the first of two parts, covers some advanced topics concerning SQL queries and functions. It is excerpted from chapter six of the book SQL DeMYSTiFied, written by Andrew Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249).

TABLE OF CONTENTS:
  1. Advanced Query Writing
  2. More Character Functions
  3. Mathematical Functions
  4. Date and Time Functions
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 38
March 02, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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



 
 
>>> More MySQL Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: