Skip to main content

CONVERT

A function that converts a given expression to a specified data type.

Synopsis

CONVERT(datatype,expression[,format-code])

{fn CONVERT(expression,datatype)}

Arguments

Argument Description
expression The expression to be converted.
datatype The data type to which expression is to be converted.
format-code Optional — An integer code that specifies date and time formats, used to convert between date/time/timestamp data types and character data types. This argument is only used with the general scalar syntax form.

Description

Two different implementations of the CONVERT function are described here. Both convert an expression in one data type to a corresponding value in another data type. Both perform date and time conversions.

Note:

The arguments in these two implementations of CONVERT are presented in a different order. The first is a general Caché scalar function compatible with MS SQL Server, which takes three arguments. The second is a Caché ODBC scalar function with two arguments. These two forms of CONVERT are handled separately in the text that follows.

  • CONVERT(datatype,expression) function supports conversion of stream data. For example, you can convert the contents of a character stream field to a string of data type VARCHAR.

  • {fn CONVERT(expression,datatype)} does not support conversion of stream data; specifying a stream field to expression results in an SQLCODE -37 error.

Specifying an invalid value to either version of CONVERT results in an SQLCODE -141.

If an expression does not have a defined data type (for example a host variable supplied by Caché ObjectScript) its data type defaults to the string data type.

For a list of the data types supported by Caché SQL, see Data Types. For other data type conversions, refer to the CAST function.

CONVERT(datatype,expression,format-code)

This is the MS SQL Server compatible function. It takes as datatype any valid Caché SQL data type, including character stream data. For a list of the data types supported by Caché SQL, see Data Types.

You can truncate a string by performing a VARCHAR-to-VARCHAR conversion, specifying an output string length shorter than the expression string length.

When using CONVERT (or CAST), if a character data type (such as CHAR or VARCHAR) has no specified length, the default maximum length is 30 characters. If a binary data type (such as BINARY or VARBINARY) has no specified length, the default maximum length is 30 characters. Otherwise, these data types with no specified length are mapped to a MAXLEN of 1 character, as shown in the Data Types table.

You can perform a BIT data type conversion. The permitted values are 1, 0, or NULL. If you specify any other value, Caché issues an SQLCODE -141 error. In the following Embedded SQL example, both are BIT conversions of a NULL:

  SET a=""
  &sql(SELECT CONVERT(BIT,:a),
       CONVERT(BIT,NULL)
       INTO :x,:y)
  WRITE !,"SQLCODE=",SQLCODE
  WRITE !,"the host variable is:",x
  WRITE !,"the NULL keyword is:",y

The optional format-code argument specifies a date, datetime, or time format. This format can either be used to define the output when converting from a date/time/timestamp data type to a character string, or to define the input when converting from a character string to a date/time/timestamp data type. The following format codes are supported; format codes that output a two-digit year are listed in the first column; formats that either output a four-digit year or do not output a year at all are listed in the second column:

Two-digit year codes Four-digit year codes Format
  0 or 100 Mon dd yyyy hh:mmAM (or PM)
1 101 mm/dd/yy
2 102 yy.dd.mm
3 103 dd/mm/yy
4 104 dd.mm.yy
5 105 dd-mm-yy
6 106 dd Mon yy
7 107 Mon dd, yy (no leading zero when dd < 10)
  8 or 108 hh:mm:ss
  9 or 109 Mon dd yyyy hh:mm:ss:nnnAM (or PM)
10 110 mm-dd-yy
11 111 yy.mm.dd
12 112 yymmdd
  13 or 113 dd Mon yyyy hh:mm:ss:nnn (24 hour)
  14 or 114 hh:mm:ss.nnn (24 hour)
  20 or 120 yyyy-mm-dd hh:mm:ss (24 hour)
  21 or 121 yyyy-mm-dd hh:mm:ss.nnnn (24 hour)
  126 yyyy-mm-ddThh:mm:ss.nnnn (24 hour)
  130 dd Mon yyyy hh:mm:ss:nnnAM (or PM)
  131 dd/mm/yyyy hh:mm:ss:nnnAM (or PM)

The following are features of date and time conversions:

  • Range of Values: The range of permitted dates is 1840-12-31 through 9999-12-31.

  • Default Values:

    • When converting a time value to TIMESTAMP, DATETIME, or SMALLDATETIME, the date defaults to 1900-01-01. Note that for {fn CONVERT()} the date defaults to the current date.

    • When converting a date value to TIMESTAMP, DATETIME, or SMALLDATETIME, the time defaults to 00:00:00.

  • Default Format: If format-code is not specified, CONVERT attempts to determine the format from the specified value. If it cannot, it defaults to format-code 100.

  • Two-digit Years: Two-digit years from 00 through 49 are converted to 21st century dates (2000 through 2049); two-digit years from 50 through 99 are converted to 20th century dates (1950 through 1999).

  • Fractional seconds: fractional seconds can be preceded by either a period (.) or a colon (:). The symbols have different meanings:

    • A period is the default, and can be used with all format codes. A period indicates a standard fraction; thus 12:00:00.4 indicates four-tenths of a second, and 12:00:00.004 indicates four-thousandth of a second. There is no limit on the number of digits of fractional precision.

    • A colon can only be used with the following format-code values: 9/109, 13/113, 130, and 131. A colon indicates that the number that follows is in thousandths of a second; thus 12:00:00:4 indicates four-thousandth of a second (12:00:00.004). The permitted number of digits following a colon is limited to three.

Specifying an expression with an invalid format or a format that does not match the format-code generates an SQLCODE -141 error. Specifying a non-existent format-code returns 1900-01-01 00:00:00.

{fn CONVERT(expression,datatype)}

This is the ODBC scalar function. It supports the following ODBC explicit data type conversions. You must use the “SQL_” keywords for specifying data type conversions with this form of CONVERT. In the following table, where there are two groups of conversion data types, the first group converts both the data value and the data type, the second group converts the data type but does not convert the data value:

Source Conversion
Any numeric data type SQL_VARCHAR, SQL_DOUBLE, SQL_DATE, SQL_TIME
%String SQL_DATE, SQL_TIME, SQL_TIMESTAMP
%Date

SQL_VARCHAR, SQL_TIMESTAMP

SQL_INTEGER, SQL_BIGINT, SQL_SMALLINT, SQL_TINYINT, SQL_DATE

%Time

SQL_VARCHAR, SQL_TIMESTAMP

SQL_VARCHAR, SQL_INTEGER, SQL_BIGINT, SQL_SMALLINT, SQL_TINYINT, SQL_TIME

%TimeStamp

SQL_DATE, SQL_TIME

SQL_VARCHAR, SQL_INTEGER, SQL_BIGINT, SQL_SMALLINT, SQL_TINYINT

Any non-stream data type SQL_INTEGER, SQL_BIGINT, SQL_SMALLINT, SQL_TINYINT
Any non-stream data type SQL_DOUBLE

SQL_VARCHAR is the standard ODBC representation. When converting to SQL_VARCHAR, dates and times are converted to their appropriate ODBC representations; numeric datatype values are converted to a string representation. When converting from SQL_VARCHAR, the value must be a valid ODBC Time, Timestamp, or Date representation.

  • When converting a time value to SQL_TIMESTAMP, the date defaults to the current date. Note that for CONVERT() the date defaults to 1900-01-01.

  • When converting a date value to SQL_TIMESTAMP, the time defaults to 00:00:00.

In this syntactic form, fractional seconds can be preceded by either a period (.) or a colon (:). The symbols have different meanings. A period indicates a standard fraction; thus 12:00:00.4 indicates four-tenths of a second, and 12:00:00.004 indicates four-thousandth of a second. A colon indicates that what follows is in thousandths of a second; thus 12:00:00:4 indicates four-thousandth of a second. The permitted number of digits following a colon is limited to three.

When converting to an integer data type or the SQL_DOUBLE data type, data values (including dates and times) are converted to a numeric representation. For SQL_DATE, this is the number of days since January 1, 1841. For SQL_TIME, this is the number of seconds since midnight. Input strings are truncated when a nonnumeric character is encountered. The integer data types also truncates decimal digits, returning the integer portion of the number.

{fn CONVERT(expression,datatype)} does not support conversion of stream data; specifying a stream field to expression results in an SQLCODE -37 error.

A NULL converted to any data type remains NULL.

An empty string (''), or any nonnumeric string value converts as follows:

  • SQL_VARCHAR and SQL_TIMESTAMP return the supplied value.

  • Numeric data types convert to 0 (zero).

  • SQL_DATE and SQL_TIME convert to NULL.

For other data type conversions, refer to the CAST function.

CONVERT Class Method

You can also perform data type conversions using the CONVERT()Opens in a new tab method call, using “SQL_” keywords for specifying data types:

$SYSTEM.SQL.CONVERT(expression,convert-to-type,convert-from-type)

as shown in the following example:

  WRITE $SYSTEM.SQL.CONVERT(60945,"SQL_VARCHAR","SQL_DATE")

Examples

CONVERT() Examples

The following examples uses the Caché scalar syntactical form of CONVERT.

The following example compares the conversion of a fractional number using the DECIMAL and DOUBLE data types:

SELECT CONVERT(DECIMAL,-123456789.0000123456789) AS DecimalVal,
       CONVERT(DOUBLE,-123456789.0000123456789) AS DoubleVal

The following example converts a character stream field to a VARCHAR text string. It also displays the length of the character stream field using CHAR_LENGTH:

SELECT Notes,CONVERT(VARCHAR(80),Notes) AS NoteText,CHAR_LENGTH(Notes) AS TextLen
FROM Sample.Employee WHERE Notes IS NOT NULL

The following example shows several conversions of the date-of-birth field (DOB) to a formatted character string:

SELECT DOB,
       CONVERT(VARCHAR(20),DOB) AS DOBDefault,
       CONVERT(VARCHAR(20),DOB,100) AS DOB100,
       CONVERT(VARCHAR(20),DOB,107) AS DOB107,
       CONVERT(VARCHAR(20),DOB,114) AS DOB114,
       CONVERT(VARCHAR(20),DOB,126) AS DOB126
FROM Sample.Person

The default format and the code 100 format are the same. Because the DOB field does not contain a time value, formats that display time (here including the default, 100, 114, and 126) supply a zero value, which represents 12:00AM (midnight). The code 126 format provides a date and time string that contains no spaces.

{fn CONVERT()} Examples

The following examples uses the ODBC syntactical form of CONVERT.

The following embedded SQL example converts a mixed string to an integer. Caché truncates the string at the first nonnumeric character and then converts the resulting numeric to canonical form:

  SET a="007 James Bond"
  &sql(SELECT {fn CONVERT(:a,SQL_INTEGER)} INTO :x)
  WRITE !,"SQLCODE=",SQLCODE
  WRITE !,"the host variable is:",x

returns the integer 7.

The following example converts dates in the "DOB" (Date Of Birth) column to the SQL_TIMESTAMP data type.

SELECT DOB,{fn CONVERT(DOB,SQL_TIMESTAMP)} AS DOBtoTstamp
     FROM Sample.Person

The resulting timestamp is in the format: yyyy-mm-dd hh:mm:ss.

The following example converts dates in the "DOB" (Date Of Birth) column to the SQL_INTEGER data type.

SELECT DOB,{fn CONVERT(DOB,SQL_INTEGER)} AS DOBtoInt
     FROM Sample.Person

The resulting integer is the $HOROLOG count of days since December 31, 1840.

The following example converts dates in the "DOB" (Date Of Birth) column to the SQL_VARCHAR data type.

SELECT DOB,{fn CONVERT(DOB,SQL_VARCHAR)} AS DOBtoVChar
     FROM Sample.Person

The resulting string is in the format: yyyy-mm-dd.

See Also

FeedbackOpens in a new tab