ASP - SQL Server Conversion Functions - Asked By m j on 19-Jul-14 11:59 PM

Can anyone point me in the direction of a resource that has a cross reference listing of all the conversion datatypes for using the convert() function in sql server?  I would greatly appreciate it.

I believe CONVERT supports... - Asked By Robbe Morris on 26-Jul-01 12:25 PM

...any SQL Server datatype.  You can get a quick list
in the Enterprise Manager table design screen next to the column name.

SQL Books Online - Asked By Desert Ghost on 26-Jul-01 12:25 PM

Have you tried SQL Books Online (comes with the program).  I'm personally not a big fan of them, but the DBAs here swear by them.

Thanks, Robbe, but... - Asked By m j on 20-Jul-14 12:00 AM

What I need is if i need to a conversion function, the datatype number to convert it to.

e.g. convert(varchar, myValue, 108)

how do i find what format 108 will return.
Check here... - Asked By Robbe Morris on 26-Jul-01 12:34 PM
I just looked in SQL Server Books Online.
type in CONVERT in the search.  About 10 lines down you'll see a listing for

CAST and CONVERT

There is a table there of the "styles" you are looking for.
It Figures - Asked By m j on 20-Jul-14 12:00 AM
the computer I have doesn't have the books online, 

oh well, time to check another computer, 
Not quite sure how this will look... - Asked By Robbe Morris on 26-Jul-01 12:45 PM
But here is the Copy/Paste.

CAST and CONVERT
Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

Syntax
Using CAST:

CAST ( expression AS data_type ) 

Using CONVERT:

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

Arguments
expression

Is any valid Microsoft® SQL Server™ expression. For more information, see Expressions. 

data_type

Is the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used. For more information about available data types, see Data Types. 

length

Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. 

style

Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

SQL Server supports the date format in Arabic style, using Kuwaiti algorithm.

In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

Without century (yy) With century (yyyy) 
Standard 
Input/Output** 
- 0 or 100 (*)  Default mon dd yyyy hh:miAM (or PM) 
1 101 USA mm/dd/yy 
2 102 ANSI yy.mm.dd 
3 103 British/French dd/mm/yy 
4 104 German dd.mm.yy 
5 105 Italian 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:mi:ss:mmmAM (or PM) 
10 110 USA mm-dd-yy 
11 111 JAPAN yy/mm/dd 
12 112 ISO yymmdd 
- 13 or 113 (*)  Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) 
14 114 - hh:mi:ss:mmm(24h) 
- 20 or 120 (*)  ODBC canonical yyyy-mm-dd hh:mi:ss(24h) 
- 21 or 121 (*)  ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h) 
- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces) 
- 130* Kuwaiti dd mon yyyy hh:mi:ss:mmmAM 
- 131* Kuwaiti dd/mm/yy hh:mi:ss:mmmAM 


*    The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).
** Input when converting to datetime; output when converting to character data.
*** Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the table. For conversion from float, money, or smallmoney to character data, the output is equivalent to style 2. For conversion from real to character data, the output is equivalent to style 1.



Important  By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on OLE Automation objects, use a cutoff year of 2030. SQL Server provides a configuration option (two digit year cutoff) that changes the cutoff year used by SQL Server and allows the consistent treatment of dates. The safest course, however, is to specify four-digit years.


When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when converting from datetime or smalldatetime values by using an appropriate char or varchar data type length.

This table shows the style values for float or real conversion to character data.

Value Output 
0 (default) Six digits maximum. Use in scientific notation, when appropriate. 
1 Always eight digits. Always use in scientific notation. 
2 Always 16 digits. Always use in scientific notation. 


In the following table, the column on the left represents the style value for money or smallmoney conversion to character data.

Value Output 
0 (default
Muchos Gracias Señor - Asked By m j on 20-Jul-14 12:01 AM
Thank you very much for your help Robbe
You be welcome... - Asked By Robbe Morris on 26-Jul-01 12:58 PM
...