HowToForums.net

[bozoz]

http://gethelp.devx.com/techtips/info_pro/10min/10min1000/10min1000-2.asp



Date Manipulation
One of the more difficult concepts in Informix's handling of date and time values concerns the use of the variables in arithmetic or relational expressions. You can add or subtract DATE and DATETIME variables from each other. You can add or subtract an INTERVAL to a DATE or DATETIME. Table 1 shows the results of different types of operations on DATE and DATETIME values.

Table 1. Operations on DATE and DATETIME Variables

First Operand
Operation

Second Operand


Result

DATE


-


DATETIME


INTERVAL

DATETIME


-


DATE


INTERVAL

DATE


+-


INTERVAL


DATETIME

DATETIME


-


DATETIME


INTERVAL

DATETIME


+-


INTERVAL


DATETIME

INTERVAL


+


DATETIME


DATETIME

INTERVAL


+-


INTERVAL


INTERVAL

DATETIME


-


CURRENT


INTERVAL

CURRENT


-


DATETIME


INTERVAL

INTERVAL


+


CURRENT


DATETIME

CURRENT


+-


INTERVAL


DATETIME

DATETIME


+-


UNITS


DATETIME

INTERVAL


+-


UNITS


INTERVAL

INTERVAL


*/


NUMBER


INTERVAL

Notice that it's always okay to subtract one DATE or DATETIME value from another, as shown here:

CURRENT - "07/01/1950" = INTERVAL (my age)
"12/25/2000" – CURRENT = INTERVAL (how long till Xmas?)

In such a case, the result is always an INTERVAL variable. It would make no sense to add two DATE or DATETIME values together. What could such an addition represent?
UNITS Keyword
When working with INTERVAL values, sometimes it is necessary to specify the precision with which you are dealing. For example, suppose you have the following field defined:

lead_time INTERVAL DAY to DAY

To add 10 days to the lead time you could use a SQL statement like this:

SELECT lead_time + INTERVAL(10) DAY to DAY
FROM orders

You could achieve the same results using the UNITS keyword:

SELECT lead_time + 10 UNITS DAY
FROM orders

Like most other programming languages, SQL often allows you to achieve the same ends with different statements. Sometimes the choice is one of personal style. Other times, one format fits in better with a structured style of code writing than another format does.
Functions
There are several built-in functions that affect date and time calculations. They can apply to either DATE or DATETIME values, with some exceptions.
TODAY
The TODAY function returns a DATE data value representing the current date. For example, you could execute a SQL function like this:

UPDATE member SET change_date = TODAY
WHER member_number = 12345

CURRENT
The CURRENT function is similar to the TODAY function, except it returns a DATETIME value. Without specific qualifiers, the default is YEAR to FRACTION(3). You can change the precision by using the same YEAR to FRACTION qualifiers as you use for DATETIMES. Thus, this would be legal:

SELECT * from member
WHERE elapsed_time < CURRENT YEAR to DAY

DATE
The DATE function takes as input a non-DATE value such as CHAR, DATETIME, or INTEGER and returns the corresponding DATE value. For example, the following SQL translates a CHARACTER value to a DATE:

SELECT * from member
WHERE enrollment_date > DATE('01/01/99')

DAY
This function returns an integer representing the day of the month. Here's an example:

SELECT * from member
WHERE DAY(enrollment_date) > DAY(CURRENT)

MONTH
This performs like the DAY function except it returns an integer between 1 and 12 representing the month:

SELECT * from member
WHERE enrollment_date > MONTH('01/01/99')

WEEKDAY
This returns an integer representing the day of the week, with 0 being Sunday and 6 being Saturday:

SELECT * from member
WHERE WEEKDAY(enrollment_date) > WEEKDAY(CURRENT)

YEAR
This function is like the ones above, only it returns a four-digit integer representing the year.
EXTEND
This function allows you to use different precisions in a DATETIME than you have specified in the declaration of the variable. It uses the same FIRST to LAST syntax as the DATETIME variables. This function is used to adjust the precision of a DATETIME variable to match the precision of an INTERVAL that you are using in a calculation. If the INTERVAL value has fields that are not part of the DATETIME value that you are using in a calculation, use the EXTEND function to adjust the precision of the DATETIME. EXTEND can either increase or decrease the precision of a DATETIME, depending upon the FIRST and LAST values.

Suppose myvariable is declared as DATETIME YEAR to DAY. If you want to add or subtract an INTERVAL defined as MINUTE, you first have to extend the DATETIME as follows:

SELECT EXTEND(myvariable, YEAR to MINUTE) –
INTERVAL(5) MINUTE to MINUTE
FROM member

The resulting value will be DATETIME YEAR to MINUTE.
MDY
The MDY function converts three-integer values into a DATE format. The first integer is the month and must evaluate to an integer in the range 1–12. The second integer is the day and must evaluate to a number in the range from 1 to however many days are in the particular month (28–31). The third expression is the year and must be a four-digit integer. Thus, the following MDY functions would each be valid:

MDY(7,1,1950)

returns a DATE of "07/01/50"

MDY(MONTH(TODAY), 1, YEAR(TODAY))

returns a DATE equal to the first day of the current month in the current year

Informix has extensive capabilities for manipulating dates and times, which can make for long and complex SQL statements. Using the three time-related data types and the time-related functions and keywords, you can accomplish almost any type of manipulation of time data. Unfortunately, getting there may be cryptic and painful. If you regularly do extensive date and time manipulation, you should understand all of the intricacies of these data structures.

Have fun!