Conversion of Date-Time or Interval Representations

Home | Previous Page | Next Page Data Manipulation > Using Date and Time Data Types > Date-Time or Interval Data >

Conversion of Date-Time or Interval Representations

Both the DataBlade API library and the ESQL/C library provide functions that convert from the text (string) representation of a date, time, or interval value to the binary (internal) representation for DATETIME or INTERVAL, respectively.

DataBlade API Functions for Date-Time or Interval Conversion

The DataBlade API provides the following functions for conversion between text and binary representations of date-time or interval data.

DataBlade API Function	Convert from	Convert to
mi_datetime_to_string( )	DATETIME (mi_datetime)	Date-time string
mi_interval_to_string( )	INTERVAL (mi_interval)	Interval string
mi_string_to_datetime( )	Date-time string	DATETIME (mi_datetime)
mi_string_to_interval( )	Interval string	INTERVAL (mi_interval)

The mi_datetime_to_string( ), mi_interval_to_string( ), mi_string_to_datetime( ), and mi_string_to_interval( ) functions convert DATETIME and INTERVAL values to and from the ANSI SQL standards formats for these data types.

Server Only

The mi_datetime_to_string( ), mi_interval_to_string( ), mi_string_to_datetime( ), and mi_string_to_interval( ) functions are useful in the input and output support functions of an opaque data type that contains mi_datetime and mi_interval values, as long as these values use the ANSI SQL formats. They enable you to convert DATETIME and INTERVAL values between their external format (text) and their internal (binary) format when transferred to and from client applications. For more information, see Conversion of Opaque-Type Data Between Text and Binary Representations.

End of Server Only

ESQL/C Functions for Date, Time, and Interval Conversion

The ESQL/C function library provides functions for conversion between text and binary representations of date, time, and interval data.

Data Conversion for DATETIME Values

The ESQL/C library provides the following functions that convert internal DATETIME (mi_datetime) values to and from char strings.

Function Name: Description
dtcvasc( ): Converts an ANSI-compliant character string to an mi_datetime value
dtcvfmtasc( ): Converts a character string to an mi_datetime value
dtextend( ): Changes the qualifier of an mi_datetime value
dttoasc( ): Converts an mi_datetime value to an ANSI-compliant character string
dttofmtasc( ): Converts an mi_datetime value to a character string

The dttoasc( ) and dtcvasc( ) functions convert mi_datetime values to and from the ANSI SQL standard values for DATETIME strings. The ANSI SQL standards specify qualifiers and formats for character representations of DATETIME and INTERVAL values. The standard qualifier for a DATETIME value is YEAR TO SECOND, and the standard format is as follows:

YYYY-MM-DD HH:MM:SS

The dttofmtasc( ) and dtcvfmtasc( ) functions convert mi_datetime values to and from a date-time string using a time-formatting mask. This time-formatting mask contains the same formatting directives that the DBTIME environment variable supports. (For a list of these directives, see the description of DBTIME in the IBM Informix: Guide to SQL Reference.)

The dtextend( ) function extends an mi_datetime value to a different qualifier. You can use it to convert between DATETIME and DATE values.

To convert a DATETIME value to a DATE value

Use dtextend( ) to adjust the DATETIME qualifier to year to day.
Apply dttoasc( ) to create a character string in the form yyyy-mm-dd.
Use rdefmtdate( ) with a pattern argument of yyyy-mm-dd to convert the string to a DATE value.

To convert a DATE value into a DATETIME value

Declare a variable with a qualifier of year to day (or initialize the qualifier with the value that the TU_DTENCODE (TU_YEAR,TU_DAY) macro returns).
Use rfmtdate( ) with a pattern of yyyy-mm-dd to convert the DATE value to a character string.
Use dtcvasc( ) to convert the character string to a value in the prepared DATETIME variable.
If necessary, use dtextend( ) to adjust the DATETIME qualifier.

Data Conversion for INTERVAL Values

The ESQL/C library provides the following functions that convert internal INTERVAL (mi_interval) values to and from char text.

Function Name: Description
incvasc( ): Converts an ANSI-compliant character string to an interval value
incvfmtasc( ): Converts a character string to an interval value
intoasc( ): Converts an interval value to an ANSI-compliant character string
intofmtasc( ): Converts an interval value to a string
invextend( ): Copies an interval value under a different qualifier

The intoasc( ) and incvasc( ) functions convert mi_interval values to and from the ANSI SQL standards for INTERVAL strings. The ANSI SQL standards specify qualifiers and formats for character representations of DATETIME and INTERVAL values. The standards for an INTERVAL value specify the following two classes of intervals:

The YEAR TO MONTH class has the following format:
```
YYYY-MM
```
A subset of this format is also valid: for example, just a month interval.
The DAY TO FRACTION class has the following format:
```
DD HH:MM:SS.F
```
Any subset of contiguous fields is also valid: for example, MINUTE TO FRACTION.

The intofmtasc( ) and incvfmtasc( ) functions convert mi_interval values to and from an interval string using a time-formatting mask. This time-formatting mask contains the same formatting directives that the DBTIME environment variable supports. (For a list of these directives, see the description of DBTIME in the IBM Informix: Guide to SQL Reference.)