Time Functions

Time Functions:
 
|--+-DATE--(--non_date_expr--)---------------------------------------+--|
   +-+-DAY-----+--(--date/dtime_expr--)------------------------------+
   | +-MONTH---+                                                     |
   | +-WEEKDAY-+                                                     |
   | '-YEAR----'                                                     |
   +-EXTEND--(--date/dtime_expr--+--------------------+--)-----------+
   |                             '-,--first--TO--last-'              |
   +-MDY--(--month--,--day--,--year--)-------------------------------+
   |  (1)                                                            |
   '--------+-TO_CHAR--(--source_date-----+--+------------------+--)-'
            '-TO_DATE--(--char_expression-'  '-,--format_string-'
 

1. Dynamic Server only

DATE Function

The DATE function converts a non-DATE expression to a DATE value. The argument can be any expression that can be converted to a DATE value, usually a CHAR, DATETIME, or INTEGER value. The following WHERE clause specifies a CHAR value for the non-DATE expression:

WHERE order_date < DATE('12/31/04')

When the DATE function interprets a CHAR non-DATE expression, it expects this expression to conform to any DATE format that the DBDATE environment specifies. For example, suppose DBDATE is set to Y2MD/ when you execute the following query:

SELECT DISTINCT DATE('02/01/1998') FROM ship_info

This SELECT statement generates an error because the DATE function cannot convert this non-DATE expression. The DATE function interprets the first part of the date string (02) as the year and the second part (01) as the month.

For the third part (1998), the DATE function encounters four digits when it expects a two-digit day (valid day values must be between 01 and 31). It therefore cannot convert the value. For the SELECT statement to execute successfully with the Y2MD/ value for DBDATE, the non-DATE expression would need to be '98/02/01'. For information on the format of DBDATE, see the IBM Informix Guide to SQL: Reference.

When you specify a positive INTEGER value for the non-DATE expression, the DATE function interprets this as the number of days after December 31, 1899.

If the integer value is negative, the DATE function interprets the value as the number of days before December 31, 1899. The following WHERE clause specifies an INTEGER value for the non-DATE expression:

WHERE order_date  <  DATE(365)

The database server searches for rows with an order_date value less than December 31, 1900 (which is 12/31/1899 plus 365 days).

DAY Function

The DAY function returns an integer that represents the day of the month. The following example uses the DAY function with the CURRENT function to compare column values to the current day of the month:

WHERE DAY(order_date) > DAY(CURRENT)

MONTH Function

The MONTH function returns an integer corresponding to the month portion of its type DATE or DATETIME argument. The following example returns a number from 1 through 12 to indicate the month when the order was placed:

SELECT order_num, MONTH(order_date) FROM orders

WEEKDAY Function

The WEEKDAY function returns an integer that represents the day of the week; zero (0) represents Sunday, one (1) represents Monday, and so on. The following example lists all the orders that were paid on the same day of the week, which is the current day:

SELECT * FROM orders
   WHERE WEEKDAY(paid_date) = WEEKDAY(CURRENT)

YEAR Function

The YEAR function returns a four-digit integer that represents the year.

The following example lists orders in which the ship_date is earlier than the beginning of the current year:

SELECT order_num, customer_num FROM orders
   WHERE year(ship_date) < YEAR(TODAY)

Similarly, because a DATE value is a simple calendar date, you cannot add or subtract a DATE value with an INTERVAL value whose last qualifier is smaller than DAY. In this case, convert the DATE value to a DATETIME value.

EXTEND Function

The EXTEND function adjusts the precision of a DATETIME or DATE value. The expression cannot be a quoted string representation of a DATE value.

If you do not specify first and last qualifiers, the default qualifiers are YEAR TO FRACTION(3).

If the expression contains fields that are not specified by the qualifiers, the unwanted fields are discarded.

If the first qualifier specifies a larger (that is, more significant) field than what exists in the expression, the new fields are filled in with values returned by the CURRENT function. If the last qualifier specifies a smaller field (that is, less significant) than what exists in the expression, the new fields are filled in with constant values. A missing MONTH or DAY field is filled in with 1, and the missing HOUR to FRACTION fields are filled in with 0.

In the following example, the first EXTEND call evaluates to the call_dtime column value of YEAR TO SECOND. The second statement expands a literal DATETIME so that an interval can be subtracted from it. You must use the EXTEND function with a DATETIME value if you want to add it to or subtract it from an INTERVAL value that does not have all the same qualifiers. The third example updates only a portion of the datetime value, the hour position. The EXTEND function yields just the hh:mm part of the datetime. Subtracting 11:00 from the hours and minutes of the datetime yields an INTERVAL value of the difference, plus or minus, and subtracting that from the original value forces the value to 11:00.

EXTEND (call_dtime, YEAR TO SECOND)

EXTEND (DATETIME (1989-8-1) YEAR TO DAY, YEAR TO MINUTE)
   - INTERVAL (720) MINUTE (3) TO MINUTE

UPDATE cust_calls SET call_dtime = call_dtime -
(EXTEND(call_dtime, HOUR TO MINUTE) - DATETIME (11:00) 
HOUR TO MINUTE) WHERE customer_num = 106

MDY Function

The MDY function returns a type DATE value with three expressions that evaluate to integers that represent the month, day, and year. The first expression must evaluate to an integer that represents the number of the month (1 to 12).

The second expression must evaluate to an integer that represents the number of the day of the month (1 to 28, 29, 30, or 31, as appropriate for the month).

The third expression must evaluate to a four-digit integer that represents the year. You cannot use a two-digit abbreviation for the third expression. The following example sets the paid_date associated with the order number 8052 equal to the first day of the present month:

UPDATE orders SET paid_date = MDY(MONTH(TODAY), 1, YEAR(TODAY))
   WHERE po_num = '8052'

TO_CHAR Function (IDS)

The TO_CHAR function converts a DATE or DATETIME value to a character string. The character string contains the date that was specified in the source_date parameter and represents this date in the format that was specified in the format_string parameter.

Any argument to this function must be of a built-in data type.

If the value of the source_date parameter is NULL, the function returns a NULL value.

If you omit the format_string parameter, the TO_CHAR function uses the default date format to format the character string. The default date format is specified by environment variables such as GL_DATETIME and GL_DATE.

The format_string parameter does not have to imply the same qualifiers as the source_date parameter. When the implied formatting mask qualifier in format_string is different from the qualifier in source_date, the TO_CHAR function extends the DATETIME value as if it had called the EXTEND function.

In the following example, the user wants to convert the begin_date column of the tab1 table to a character string. The begin_date column is defined as a DATETIME YEAR TO SECOND data type. The user uses a SELECT statement with the TO_CHAR function to perform this conversion:

SELECT TO_CHAR(begin_date, '%A %B %d, %Y %R') FROM tab1

The symbols in the format_string parameter in this example have the following meanings. For a complete list of format symbols and their meanings, see the GL_DATE and GL_DATETIME environment variables in the IBM Informix GLS User's Guide.

Symbol

Meaning

%A

Full weekday name as defined in the locale

%B

Full month name as defined in the locale

%d

Day of the month as a decimal number

%Y

Year as a 4-digit decimal number

%R

Time in 24-hour notation

The result of applying the specified format_string to the begin_date column is as follows:

Wednesday July 23, 1997 18:45

TO_DATE Function (IDS)

The TO_DATE function converts a character string to a DATETIME value. The function evaluates the char_expression parameter as a date according to the date format you specify in the format_string parameter and returns the equivalent date. If char_expression is NULL, then a NULL value is returned.

Any argument to the TO_DATE function must be of a built-in data type.

If you omit the format_string parameter, the TO_DATE function applies the default DATETIME format to the DATETIME value. The default DATETIME format is specified by the GL_DATETIME environment variable.

In the following example, the user wants to convert a character string to a DATETIME value in order to update the begin_date column of the tab1 table with the converted value. The begin_date column is defined as a DATETIME YEAR TO SECOND data type. The user uses an UPDATE statement that contains a TO_DATE function to accomplish this result:

UPDATE tab1
   SET begin_date = TO_DATE('Wednesday July 23, 1997 18:45',
   '%A %B %d, %Y %R');

The format_string parameter in this example tells the TO_DATE function how to format the converted character string in the begin_date column. For a table that shows the meaning of each format symbol in this format string, see TO_CHAR Function (IDS).