DATETIME and INTERVAL Library Functions
The following C functions are available in ESQL/C to handle datetime and interval host variables.
When you compile your ESQL/C program with the esql command, esql automatically links these functions into your program. The following pages describe each of these functions.
dtaddinv()
The dtaddinv() function adds an interval value to a datetime value. The result is a datetime value.
Syntax
Usage
The dtaddinv() function adds the interval value in inv to the datetime value in dt and stores the datetime value in res. This result inherits the qualifier of dt.
The interval value must be in either the year to month or day to fraction(5) ranges.
The datetime value must include all the fields present in the interval value.
If you do not initialize the variables dt and inv, the function might return an unpredictable result.
Return Codes
Example
The demo directory contains this sample program in the dtaddinv.ec file.
Example Output
dtcurrent()
The dtcurrent() function assigns the current date and time to a datetime variable.
Syntax
Usage
When the variable qualifier is set to zero (or any invalid qualifier), the dtcurrent() function initializes it with the year to fraction(3) qualifier.
When the variable contains a valid qualifier, the dtcurrent() function extends the current date and time to agree with the qualifier.
Example Calls
The following statements set the host variable timewarp to the current date:
The following statements set the variable now to the current time, to the nearest millisecond:
Example
The demo directory contains this sample program in the dtcurrent.ec file.
Example Output
dtcvasc()
The dtcvasc() function converts a string that conforms to ANSI SQL standard for a DATETIME value to a datetime value. For information about the ANSI SQL DATETIME standard, see page 6-36.
Syntax
Usage
You must initialize the datetime variable in dtvalue with the qualifier that you want this variable to have.
The character string in inbuf must have values that conform to the year to second qualifier in the ANSI SQL format. The inbuf string can have leading and trailing spaces. However, from the first significant digit to the last, inbuf can only contain characters that are digits and delimiters that conform to the ANSI SQL standard for DATETIME values.
If you specify a year value as one or two digits, the dtcvasc() function assumes that the year is in the 20th century (it adds 1900 to the year value). You can set the DBCENTURY environment variable to determine which century dtcvasc() uses when you omit a century from the date.
If the character string is an empty string, the dtcvasc() function sets to null the value to which dtvalue points. If the character string is acceptable, the function sets the value in the datetime variable and returns zero. Otherwise, the function leaves the variable unchanged and returns a negative error code.
Return Codes
Example Call
The following example initializes the host variable leap_day to the extra day of leap year 1996:
Example
The demo directory contains this sample program in the dtcvasc.ec file.
Example Output
dtcvfmtasc()
The dtcvfmtasc() function uses a formatting mask to convert a character string to a datetime value.
Syntax
Usage
You must initialize the datetime variable in dtvalue with the qualifier that you want this variable to have. The datetime variable does not need to specify the same qualifier that the formatting mask implies. When the datetime qualifier is different from the implied formatting-mask qualifier, dtcvfmtasc() extends the datetime value (as if it had called the dtextend() function).
All qualifier fields in the character string in inbuf must be contiguous. In other words, if the qualifier is hour to second, you must specify all values for hour, minute, and second somewhere in the string, or the dtcvfmtasc() function returns an error.
The inbuf character string can have leading and trailing spaces. However, from the first significant digit to the last, inbuf can contain only digits and delimiters that are appropriate for the qualifier fields that the formatting mask implies. For more information on acceptable digits and delimiters for a DATETIME value, see the "ANSI SQL Standards for DATETIME and INTERVAL Values".
The dtcvfmtasc() function returns an error if the formatting mask, fmtstring, is an empty string. If fmtstring is a null pointer, the dtcvfmtasc() function must determine the format to use when it reads the character string in inbuf. When you use the default locale, the function uses the following precedence:
1. The format that the DBTIME environment variable specifies (if DBTIME is set). For more information on DBTIME, refer to the Informix Guide to SQL: Reference.
2. The format that the GL_DATETIME environment variable specifies (if GL_DATETIME is set). For more information on GL_DATETIME, refer to the Informix Guide to GLS Functionality.
3. The default date format conforms to the standard ANSI SQL format:
%iY-%m-%d %H:%M:%S
The ANSI SQL format specifies a qualifier of year to second for the output. You can express the year as four digits (1995) or as two digits (95). When you use a two-digit year (%y) in a formatting mask, the dtcvfmtasc() function uses the value of the DBCENTURY environment variable to determine which century to use. If you do not set DBCENTURY, dtcvfmtasc() assumes the 20th century for two-digit years. For information on how to set DBCENTURY, see the Informix Guide to SQL: Reference.
When you use a nondefault locale (one other than U.S. English) and do not set the DBTIME or GL_DATETIME environment variables, dtcvfmtasc() uses the default DATETIME format that the locale defines. For more information, see the Informix Guide to GLS Functionality. 
When the character string and the formatting mask are acceptable, the dtcvfmtasc() function sets the datetime variable in dtvalue and returns zero. Otherwise, it returns an error code and the datetime variable contains an unpredictable value.
Return Codes
Example
The demo directory contains this sample program in the file dtcvfmtasc.ec. The code initializes the variable birthday to a fictitious birthday.
/* Initialize birthday2 to "07-14-88 09:15" */
printf("Birthday #2 = July 14, 1988. Time: 9:15 am\n");
x = dtcvfmtasc("July 14, 1988. Time: 9:15am",
"%B %d, %Y. Time: %I:%M%p", &birthday2);
/*Convert the internal format to ascii in ANSI format, for displaying. */
x = dttoasc(&birthday2, out_str2);
printf("Datetime (year to minute) value = %s\n\n", out_str2);
/* Initialize birthday3 to "07-14-XX 09:15" where XX is current year.
* Note that birthday3 is year to minute but this initialization only
* provides month to minute. dtcvfmtasc provides current information
* for the missing year.
*/
printf("Birthday #3 = July 14. Time: 9:15 am\n");
x = dtcvfmtasc("July 14. Time: 9:15am", "%B %d. Time: %I:%M %p",
&birthday3);
/*Convert the internal format to ascii in ANSI format, for displaying. */
x = dttoasc(&birthday3, out_str3);
printf("Datetime (year to minute) value with current year = %s\n",
out_str3);
printf("\nDTCVFMTASC Sample Program over.\n\n");
}
Example Output
dtextend()
The dtextend() function extends a datetime value to a different qualifier. Extending is the operation of adding or dropping fields of a DATETIME value to make it match a given qualifier.
Syntax
Usage
The dtextend() function copies the qualifier-field digits of the in_dt datetime variable to the out_dt datetime variable. The qualifier of the out_dt variable controls the copy.
The function discards any fields in in_dt that the out_dt variable does not include. The function fills in any fields in out_dt that are not present in in_dt, as follows:
Return Codes
Example Call
In these statements, a variable fiscal_start is set up with the first day of a fiscal year that begins on June 1. The dtextend() function generates the current year.
Example
The demo directory contains this sample program in the file dtextend.ec.
Example Output
dtsub()
The dtsub() function subtracts one datetime value from another. The result is an interval value.
Syntax
Usage
The dtsub() function subtracts the datetime value d2 from d1 and stores the interval result in inv. The result can be either a positive or a negative value. If necessary, the function extends d2 to match the qualifier for d1, before the subtraction.
Initialize the qualifier for inv with a value in either the year to month or day to fraction(5) classes. When d1 contains fields in the day to fraction class, the interval qualifier must also be in the day to fraction class.
Return Codes
Example
The demo directory contains this sample program in the file dtsub.ec. The program performs datetime subtraction that returns equivalent interval results in the range of year to month and month to month and attempts to return an interval result in the range day to hour.
Example Output
dtsubinv()
The dtsubinv() function subtracts an interval value from a datetime value. The result is a datetime value.
Syntax
Usage
The dtsubinv() function subtracts the interval value in inv from the datetime value in dt and stores the datetime value in res. This result inherits the qualifier of dt.
The datetime value must include all the fields present in the interval value. When you do not initialize the variables dt and inv, the function might return an unpredictable result.
Return Codes
Example
The demo directory contains this sample program in the file dtsubinv.ec.
Example Output
dttoasc()
The dttoasc() function converts the field values of a datetime variable to an ASCII string that conforms to ANSI SQL standards. For information about the ANSI SQL DATETIME standard, see page 6-36.
Syntax
Usage
The dttoasc() function converts the digits of the fields in the datetime variable to their character equivalents and copies them to the outbuf character string with delimiters (hyphen, space, colon, or period) between them. You must initialize the datetime variable in dtvalue with the qualifier that you want the character string to have.
The character string does not include the qualifier or the parentheses that SQL statements use to delimit a DATETIME literal. The outbuf string conforms to ANSI SQL standards. It includes one character for each delimiter, plus the fields, which are of the following sizes.
A datetime value with the year to fraction(5) qualifier produces the maximum length of output. The string equivalent contains 19 digits, 6 delimiters, and the null terminator, for a total of 26 bytes:
If you do not initialize the qualifier of the datetime variable, the dttoasc() function returns an unpredictable value, but this value does not exceed 26 bytes.
Return Codes
Example
The demo directory contains this sample program in the file dttoasc.ec.
Example Output
dttofmtasc()
The dttofmtasc() function uses a formatting mask to convert a datetime variable to a character string.
Syntax
Usage
You must initialize the datetime variable in dtvalue with the qualifier that you want the character string to have. If you do not initialize the datetime variable, the function returns an unpredictable value. The character string in outbuf does not include the qualifier or the parentheses that SQL statements use to delimit a DATETIME literal.
The formatting mask, fmtstring, does not need to imply the same qualifiers as the datetime variable. When the implied formatting-mask qualifier is different from the datetime qualifier, dttofmtasc() extends the datetime value (as if it called the dtextend() function).
If the formatting mask is an empty string, the function sets character string, outbuf, to an empty string. If fmtstring is a null pointer, the dttofmtasc() function must determine the format to use for the character string in outbuf. When you use the default locale, the function uses the following precedence:
1. The format that the DBTIME environment variable specifies (if DBTIME is set). For more information on DBTIME, refer to the Informix Guide to SQL: Reference.
2. The format that the GL_DATETIME environment variable specifies (if GL_DATETIME is set). For more information on GL_DATETIME, refer to the Informix Guide to GLS Functionality.
3. The default date format that conforms to the standard ANSI SQL
format:
%iY-%m-%d %H:%M:%S
When you use a two-digit year (%y) in a formatting mask, the dttofmtasc() function uses the value of the DBCENTURY environment variable to determine which century to use. If you do not set DBCENTURY, dttofmtasc() assumes the 20th century for two-digit years. For information on how to set DBCENTURY, see the Informix Guide to SQL: Reference.
When you use a nondefault locale (one other than U.S. English) and do not set the DBTIME or GL_DATETIME environment variables, dttofmtasc() uses the default DATETIME format that the client locale defines. For more information, see the Informix Guide to GLS Functionality.
Return Codes
Example
The demo directory contains this sample program in the file dttofmtasc.ec.
/* *dttofmtasc.ec*
The following program illustrates the conversion of a datetime
value into strings of different formats.
*/
#include <stdio.h>
EXEC SQL include datetime;
main()
{
char out_str1[25];
char out_str2[25];
char out_str3[30];
int x;
EXEC SQL BEGIN DECLARE SECTION;
datetime month to minute birthday;
EXEC SQL END DECLARE SECTION;
printf("DTTOFMTASC Sample ESQL Program runnin.\n\n");
/* Initialize birthday to "09-06 13:30" */
printf("Birthday datetime (month to minute) value = ");
printf("September 6 at 01:30 pm\n");
x = dtcvfmtasc("September 6 at 01:30 pm","%B %d at %I:%M %p",
&birthday);
/* Convert the internal format to ascii for 3 given display formats.
* Note that the second format does not include the minutes field and that
* the last format includes a year field even though birthday was not
* initialized as year to minute.
*/
x = dttofmtasc(&birthday, out_str1, sizeof(out_str1),
"%d %B at %H:%M");
x = dttofmtasc(&birthday, out_str2, sizeof(out_str2),
"%d %B at %H");
x = dttofmtasc(&birthday, out_str3, sizeof(out_str3),
"%d %B, %Y at%H:%M");
Example Output
incvasc()
The incvasc() function converts a string that conforms to the ANSI SQL standard for an INTERVAL value to an interval value. For information about the ANSI SQL interval standard, see page 6-36.
Syntax
Usage
You must initialize the interval variable in invvalue with the qualifier that you want this variable to have.
The character string in inbuf can have leading and trailing spaces. However, from the first significant digit to the last, inbuf can only contain characters that are digits and delimiters that are appropriate to the qualifier fields of the interval variable.
If the character string is an empty string, the incvasc() function sets the value in invvalue to null. If the character string is acceptable, the function sets the value in the interval variable and returns zero. Otherwise, the function sets the value in the interval value to null.
Return Codes
Example
The demo directory contains this sample program in the file incvasc.ec.
Example Output
incvfmtasc()
The incvfmtasc() function uses a formatting mask to convert a character string to an interval value.
Syntax
Usage
You must initialize the interval variable in invvalue with the qualifier you want this variable to have. The interval variable does not need to specify the same qualifier as the formatting mask. When the interval qualifier is different from the implied formatting-mask qualifier, incvfmtasc() converts the result to appropriate units as necessary. However, both qualifiers must belong to the same interval class: either the year to month class or the day to fraction class.
All fields in the character string in inbuf must be contiguous. In other words, if the qualifier is hour to second, you must specify all values for hour, minute, and second somewhere in the string, or incvfmtasc() returns an error.
The inbuf character string can have leading and trailing spaces. However, from the first significant digit to the last, inbuf can contain only digits and delimiters that are appropriate for the qualifier fields that the formatting mask implies. For more information on acceptable digits and delimiters for an INTERVAL value, see "ANSI SQL Standards for DATETIME and INTERVAL Values".
If the character string is acceptable, the incvfmtasc() function sets the interval value in invvalue and returns zero. Otherwise, the function returns an error code and the interval variable contains an unpredictable value.
The formatting directives %B, %b, and %p, which the DBTIME environment variable accepts, are not applicable in fmtstring because month name and A.M./P.M. information is not relevant for intervals of time. Use the %Y directive if the interval is more than 99 years (%y can handle only two digits). For hours, use %H (not %I, because %I can represent only 12 hours). If fmtstring is an empty string, the function returns an error.
Return Codes
Example
The demo directory contains this sample program in the file incvfmtasc.ec.
/* *incvfmtasc.ec*
The following program illustrates the conversion of two strings
to three interval values.
*/
#include <stdio.h>
EXEC SQL include datetime;
main()
{
char out_str[30];
char out_str2[30];
char out_str3[30];
int x;
EXEC SQL BEGIN DECLARE SECTION;
interval day to minute short_time;
interval minute(5) to second moment;
interval hour to second long_moment;
EXEC SQL END DECLARE SECTION;
printf("INCVFMTASC Sample ESQL Program running.\n\n");
/* Initialize short_time */
printf("Interval value #1 = 20 days, 3 hours, 40 minutes\n");
x = incvfmtasc("20 days, 3 hours, 40 minutes",
"%d days, %H hours, %M minutes", &short_time);
/*Convert the internal format to ascii in ANSI format, for displaying. */
x = intoasc(&short_time, out_str);
printf("Interval value (day to minute) = %s\n", out_str);
/* Initialize moment */
printf("\nInterval value #2 = 428 minutes, 30 seconds\n");
x = incvfmtasc("428 minutes, 30 seconds",
"%M minutes, %S seconds", &moment);
/* Convert the internal format to ascii in ANSI format, for displaying. */
x = intoasc(&moment, out_str2);
printf("Interval value (minute to second) = %s\n", out_str2);
/* Initialize long_moment */
printf("\nInterval value #3 = 428 minutes, 30 seconds\n");
x = incvfmtasc("428 minutes, 30 seconds",
"%M minutes, %S seconds", &long_moment);
/*Convert the internal format to ascii in ANSI format, for displaying. */
x = intoasc(&long_moment, out_str3);
printf("Interval value (hour to second) = %s\n", out_str3);
printf("\nINCVFMTASC Sample Program over.\n\n");
}
Example Output
intoasc()
The intoasc() function converts the field values of an interval variable to an ASCII string that conforms to the ANSI SQL standard. For information about the ANSI SQL DATETIME standard, see page 6-36.
Syntax
Usage
The intoasc() function converts the digits of the fields in the interval variable to their character equivalents and copies them to the outbuf character string with delimiters (hyphen, space, colon, or period) between them. You must initialize the interval variable in invvalue with the qualifier that you want the character string to have.
The character string does not include the qualifier or the parentheses that SQL statements use to delimit an INTERVAL literal. The outbuf string conforms to ANSI SQL standards. It includes one character for each delimiter (hyphen, space, colon, or period) plus fields with the following sizes.
An interval value with the day(5) to fraction(5) qualifier produces the maximum length of output. The string equivalent contains 16 digits, 4 delimiters, and the null terminator, for a total of 21 bytes:
If you do not initialize the qualifier of the interval variable, the intoasc() function returns an unpredictable value, but this value does not exceed 21 bytes.
Return Codes
Example
The demo directory contains this sample program in the file intoasc.ec.
Example Output
intofmtasc()
The intofmtasc() function uses a formatting mask to convert an interval variable to a character string.
Syntax
Usage
You must initialize the interval variable in invvalue with the qualifier that you want the character string to have. If you do not initialize the interval variable, the function returns an unpredictable value. The character string in outbuf does not include the qualifier or the parentheses that SQL statements use to delimit an INTERVAL literal.
The formatting mask, fmtstring, does not need to imply the same qualifiers as the interval variable. When the implied formatting-mask qualifier is different from the interval qualifier, intofmtasc() converts the result to appropriate units, as necessary (as if it called the invextend() function). However, both qualifiers must belong to the same class: either the year to month class or the day to fraction class.
If fmtstring is an empty string, the intofmtasc() function sets outbuf to an empty string.
The formatting directives %B, %b, and %p, which the DBTIME environment variable accepts, are not applicable in fmtstring because month name and A.M./P.M. information is not relevant for intervals of time. Use the %Y directive if the interval is more than 99 years (%y can handle only two digits). For hours, use %H (not %I, because%I can represent only 12 hours). If fmtstring is an empty string, the function returns an error.
If the character string and the formatting mask are acceptable, the incvfmtasc() function sets the interval value in invvalue and returns zero. Otherwise, the function returns an error code and the interval variable contains an unpredictable value.
Return Codes
Example
The demo directory contains this sample program in the file intofmtasc.ec.
/* Turn the interval into ascii string of a certain format. */
x = intofmtasc(&short_time, out_str, sizeof(out_str),
"%d days, %H hours, %M minutes to go!");
printf("\tFormatted value: %s\n", out_str);
/* Initialize moment (minute(5) to second interval value */
printf("\nInterval string #2: '428 minutes, 30 seconds'\n");
x = incvfmtasc("428 minutes, 30 seconds",
"%M minutes, %S seconds", &moment);
/* Turn each interval into ascii string of a certain format. Note
* that the second and third calls to intofmtasc both use moment
* as the input variable, but the output strings have different
* formats.
*/
x = intofmtasc(&moment, out_str2, sizeof(out_str2),
"%M minutes and %S seconds left.");
x = intofmtasc(&moment, out_str3, sizeof(out_str3),
"%H hours, %M minutes, and %S seconds still left.");
/* Print each resulting string */
printf("\tFormatted value: %s\n", out_str2);
printf("\tFormatted value: %s\n", out_str3);
printf("\nINTOFMTASC Sample Program over.\n\n");
}
Example Output
invdivdbl()
The invdivdbl() function divides an interval value by a numeric value.
Syntax
Usage
The input and output qualifiers must both belong to the same interval class: either the year to month class or the day to fraction(5) class. If the qualifier for ov is different from the qualifier for iv (within the same class), the invdivdbl() function extends the result (as the invextend() function defines).
The invdivdbl() function divides the interval value in iv by num and stores the result in ov.
The value in num can be either a positive or a negative value.
Return Codes
Example
The demo directory contains this sample program in the file invdivdbl.ec.
/*
* indivdbl.ec *
The following program divides an INTERVAL type variable by a numeric value
and stores the result in an INTERVAL variable. The operation is done twice,
using INTERVALs with different qualifiers to store the result.
*/
#include <stdio.h>
EXEC SQL include datetime;
main()
{
char out_str[16];
EXEC SQL BEGIN DECLARE SECTION;
interval day to second daytosec1;
interval hour to minute hrtomin;
interval day to second daytosec2;
EXEC SQL END DECLARE SECTION;
printf("INVDIVDBL Sample ESQL Program running.\n\n");
/* Input is 3 days, 5 hours, 27 minutes, and 30 seconds */
printf("Interval (day to second) string = '3 5:27:30'\n");
incvasc("3 5:27:30", &daytosec1);
/* Divide input value by 3.0, store in hour to min interval */
invdivdbl(&daytosec1, (double) 3.0, &hrtomin);
/* Convert the internal format to ascii for displaying */
intoasc(&hrtomin, out_str);
printf("Divisor (double) = 3.0 \n");
printf("-----------------------------------------------------\n");
printf("Quotient #1 (hour to minute) = '%s'\n", out_str);
/* Divide input value by 3.0, store in day to sec interval variable */
invdivdbl(&hrtomin, (double) 3.0, &daytosec2);
/* Convert the internal format to ascii for displaying */
intoasc(&daytosec2, out_str);
printf("Quotient #2 (day to second) = '%s'\n", out_str);
printf("\nINVDIVDBL Sample Program over.\n\n");
}
Example Output
invdivinv()
The invdivinv() function divides an interval value by another interval value.
Syntax
Usage
The invdivinv() function divides the interval value in i1 by i2, and stores the result in num. The result can be either positive or negative.
Both the input and output qualifiers must belong to the same interval class: either the year to month class or the day to fraction(5) class. If necessary, the invdivinv() function extends the interval value in i2 to match the qualifier for i1 before the division.
Return Codes
Example
The demo directory contains this sample program in the file invdivinv.ec.
Example Output
invextend()
The invextend() function copies an interval value under a different qualifier. Extending is the operation of adding or dropping fields of an INTERVAL value to make it match a given qualifier. For INTERVAL values, both qualifiers must belong to the same interval class: either the year to month class or the day to fraction(5) class.
Syntax
Usage
The invextend() function copies the qualifier-field digits of in_inv interval variable to the out_inv interval variable. The qualifier of the out_inv variable controls the copy.
The function discards any fields in in_inv that are to the right of the least-significant field in out_inv. The function fills in any fields in out_inv that are not present in in_inv as follows:
Return Codes
Example
The demo directory contains this sample program in the file invextend.ec. The example illustrates interval extension. In the second result, the output contains zeros in the seconds field, and the days field has been set to 3.
Example Output
invmuldbl()
The invmuldbl() function multiplies an interval value by a numeric value.
Syntax
Usage
The invmuldbl() function multiplies the interval value in iv by num and stores the result in ov. The value in num can be either positive or negative.
Both the input and output qualifiers must belong to the same interval class: either the year to month class or the day to fraction(5) class. If the qualifier for ov is different from the qualifier for iv (but of the same class), the invmuldbl() function extends the result (as the invextend() function defines).
Return Codes
Example
The demo directory contains this sample program in the file invmuldbl.ec. The example illustrates how to multiply an interval value by a numeric value. The second multiplication illustrates the result of interval multiplication when the input and output qualifiers are different.
/*
* invmuldbl.ec *
The following program multiplies an INTERVAL type variable by a numeric value
and stores the result in an INTERVAL variable. The operation is done twice,
using INTERVALs with different qualifiers to store the result.
*/
#include <stdio.h>
EXEC SQL include datetime;
main()
{
char out_str[16];
EXEC SQL BEGIN DECLARE SECTION;
interval hour to minute hrtomin1;
interval hour to minute hrtomin2;
interval day to second daytosec;
EXEC SQL END DECLARE SECTION;
printf("INVMULDBL Sample ESQL Program running.\n\n");
/* input is 25 hours, and 49 minutes */
printf("Interval (hour to minute) = 25:49\n");
incvasc("25:49", &hrtomin1);
printf("Multiplier (double) = 3.0\n");
printf("-------------------------------------------------------\n");
/* Convert the internal format to ascii for displaying */
invmuldbl(&hrtomin1, (double) 3.0, &hrtomin2);
intoasc(&hrtomin2, out_str);
printf("Product #1 (hour to minute) = '%s'\n", out_str);
/* Convert the internal format to ascii for displaying */
invmuldbl(&hrtomin1, (double) 3.0, &daytosec);
intoasc(&daytosec, out_str);
printf("Product #2 (day to second) = '%s'\n", out_str);
printf("\nINVMULDBL Sample Program over.\n\n");
}
Example Output
|
