convert

Syntax

convert (datatype [(length) | (precision[, scale])] 
	[null | not null], expression [, style])

Parameters

• datatype – is the system-supplied datatype (for example, char(10), unichar (10), varbinary (50), or int) into which to convert the expression. You cannot use user-defined datatypes.

  When Java is enabled in the database, datatype can also be a Java-SQL class in the current database.

• length – is an optional parameter used with char, nchar, unichar, univarchar, varchar, nvarchar, binary, and varbinary datatypes. If you do not supply a length, the SAP ASE server truncates the data to 30 characters for the character types and 30 bytes for the binary types. The maximum allowable length for character and binary expression is 64K.
• precision – is the number of significant digits in a numeric or decimal datatype. For float datatypes, precision is the number of significant binary digits in the mantissa. If you do not supply a precision, the SAP ASE server uses the default precision of 18 for numeric and decimal datatypes.
• scale – is the number of digits to the right of the decimal point in a numeric, or decimal datatype. If you do not supply a scale, the SAP ASE server uses the default scale of 0.
• null | not null – specifies the nullabilty of the result expression. If you do not supply either null or not null, the converted result has the same nullability as the expression.
• expression – is the value to be converted from one datatype or date format to another.

  When Java is enabled in the database, expression can be a value to be converted to a Java-SQL class.

  When unichar is used as the destination datatype, the default length of 30 Unicode values is used if no length is specified.

• style – is the display format to use for the converted data. When converting money or smallmoney data to a character type, use a style of 1 to display a comma after every 3 digits.

  When converting datetime or smalldatetime data to a character type, use the style numbers in the following table to specify the display format. Values in the left-most column display 2-digit years (yy). For 4-digit years (yyyy), add 100, or use the value in the middle column.

  When converting date data to a character type, use style numbers 1 through 7 (101 through 107) or 10 through 12 (110 through 112) in the following table to specify the display format. The default value is 100 (mon dd yyyy hh:miAM (or PM )). If date data is converted to a style that contains a time portion, that time portion reflects the default value of zero.

  When converting time data to a character type, use style number 8 or 9 (108 or 109) to specify the display format. The default is 100 (mon dd yyyy hh:miAM (or PM)). If time data is converted to a style that contains a date portion, the default date of Jan 1, 1900 is displayed.

  Date Format Conversions Using the style Parameter

  Without Century (yy)	With Century (yyyy)	Standard	Output
  -	0 or 100	Default	mon dd yyyy hh:mm AM (or PM)
  1	101	USA	mm/dd/yy
  2	2	SQL standard	yy.mm.dd
  3	103	English/French	dd/mm/yy
  4	104	German	dd.mm.yy
  5	105		dd-mm-yy
  6	106		dd mon yy
  7	107		mon dd, yy
  8	108		HH:mm:ss
  -	9 or 109	Default + milliseconds	mon dd yyyy hh:mm:ss AM (or PM)
  10	110	USA	mm-dd-yy
  11	111	Japan	yy/mm/dd
  12	112	ISO	yymmdd
  13	113		yy/dd/mm
  14	114		mm/yy/dd
  14	114		hh:mi:ss:mmmAM(or PM)
  15	115		dd/yy/mm
  -	16 or 116		mon dd yyyy HH:mm:ss
  17	117		hh:mmAM
  18	118		HH:mm
  19			hh:mm:ss:zzzAM
  20			hh:mm:ss:zzz
  21			yy/mm/dd HH:mm:ss
  22			yy/mm/dd HH:mm AM (or PM)
  23			yyyy-mm-ddTHH:mm:ss
  36	136		hh:mm:ss.zzzzzzAM(PM)
  37	137		hh:mm.ss.zzzzzz
  38	138		mon dd yyyy hh:mm:ss.zzzzzzAM(PM)
  39	139		mon dd yyyy hh:mm:ss.zzzzzz
  40	140		yyyy-mm-dd hh:mm:ss.zzzzzz

  “mon” indicates a month spelled out, “mm” the month number or minutes. “HH ”indicates a 24-hour clock value, “hh” a 12-hour clock value. The last row, 23, includes a literal “T” to separate the date and time portions of the format.Styles 24–35 are undefined.

  The default values (style 0 or 100), and style 9 or 109 return the century (yyyy). When converting to char or varchar from smalldatetime, styles that include seconds or milliseconds show zeros in those positions.

Examples

• Example 1 – Converts the specified value in title to another datatype display format:

  select title, convert(char(12), total_sales) 
  from titles

• Example 2 – Converts the title and total sales from title:

  select title, total_sales 
  from titles 
  where convert(char(20), total_sales) like "1%"

• Example 3 – Converts the current date to style 3, dd/mm/yy:

  select convert(char(12), getdate(), 3)

• Example 4 – If the value pubdate can be null, you must use varchar rather than char, or errors may result:

  select convert(varchar(12), pubdate, 3) from titles 

• Example 5 – Returns the integer equivalent of the string “0x00000100”. Results can vary from one platform to another:

  select convert(integer, 0x00000100)

• Example 6 – Returns the platform-specific bit pattern as an SAP binary type:

  select convert (binary, 10)

• Example 7 – Returns 1, the bit string equivalent of $1.11:

  select convert(bit, $1.11)

• Example 8 – Creates #tempsales with total_sales of datatype char(100), and does not allow null values. Even if titles.total_sales was defined as allowing nulls, #tempsales is created with #tempsales.total_sales not allowing null values:

  select title, convert (char(100) not null, total_sales) into #tempsales 
  from titles

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute convert.