The convert function allows you to change the type of an expression. The syntax is

   CONVERT(expression, 'type-name'[, 'mode'])
The type name should in general be in lower case.

This can be useful in a number of situations. Some cases where you might want to use convert are

  • The display format for a different format is more useful. For example you might want to convert a field of type COUNTER to a DATE field, so you can see when the record was inserted, for example:

        SELECT convert(id, 'date')
        FROM   LOG;

  • If you have an application which is expecting data in a particular type you can use convert to make sure you will receive the correct type.

Caveat: Note that in Texis version 7 and later, convert()ing data from/to varbyte/varchar no longer converts the data to/from hexadecimal by default (as was done in earlier versions) in programs other than tsql; it is now preserved as-is (though truncated at nul for varchar). See the bintohex() and hextobin() functions (here) for hexadecimal conversion, and the hexifybytes SQL property (here) for controlling automatic hex conversion.

Also in Texis version 7 and later, an optional third argument may be given to convert(), which is a varchartostrlstsep mode value (here). This third argument may only be supplied when converting to type strlst or varstrlst. It allows the separator character or mode to be conveniently specified locally to the conversion, instead of having to alter the global varchartostrlstsep mode.

Copyright © Thunderstone Software     Last updated: Tue Feb 21 13:46:30 EST 2017
Copyright © 2017 Thunderstone Software LLC. All rights reserved.