SQL Reference - CONVERT

CONVERT(expr USING transcoding_name)

expr: This refers to the expression or a value, typically a string, that one wishes to convert to a different character set.
using transcoding_name: This refers to the target character set to which the value of expr should be converted.

Example

SELECT CONVERT('2022-10-10', DATETIME);

Output

2022-10-10 00:00:00

Explanation

In the provided SQL query, the CONVERT function is used to convert a string ‘2022-10-10’ into a DATETIME format. The output is the string converted into a DATETIME, which includes the year, month, day, and time (00:00:00).

CONVERT( data_type [ ( length ) ] , expression [ , style ] )

data_type: This signifies the target data type to which the expression is converted. Different data types that can be used include
length: This is an optional parameter that specifies the length of the target data type. For instance, if you are converting to a varchar, you can specify the maximum length of the resulting string.
expression: This is the value or values that are being converted to the target data type. It can be a constant, variable, column of a table, or return value of a built-in function.
style: This is also an optional parameter. It is used when convert from string data to a date/time or vice versa and it specifies the format of the date/time. Different style values result in different formatting.

Example

SELECT CONVERT(varchar, GETDATE(), 20) AS CurrentDate

Output

CurrentDate
-------------------
2022-09-28 18:29:47

Explanation

In the provided example, the SQL CONVERT function is used to convert the current date and time, retrieved by the GETDATE() function, into a varchar data type with a format of ‘YYYY-MM-DD HH:MI:SS’.

CONVERT(character, dest_char_set, source_char_set)

character: This parameter refers to the value to be converted. It can be a specific character, binary string, or a value of a char, varchar2, nchar, nvarchar2, clob, or nclob datatype.
dest_char_set: This designates the name of the character set to which ‘character’ is converted. The database converts the ‘character’ to a sequence of bytes according to the form identified by ‘dest_char_set’.
source_char_set: This identifies the character set in which ‘character’ is currently stored in the database. The database uses ‘source_char_set’ to interpret the sequence of bytes stored in ‘character’.

Example

SELECT CONVERT('ABCDEFG', 'UTF8', 'WE8ISO8859P1') FROM dual;

Output

'ABCDEFG'

Explanation

The CONVERT function converts a string from one character set to another. The first argument is the string to be converted. The second argument is the character set to convert to. The third argument is the character set to convert from. In this case, the string ‘ABCDEFG’ is converted from ‘WE8ISO8859P1’ to ‘UTF8’, since it’s already in UTF8, the output remains ‘ABCDEFG’.