Shulou(Shulou.com)05/31 Report--

SQL Convert method how to use, in response to this problem, this article details the corresponding analysis and solution, hoping to help more want to solve this problem of small partners to find a simpler and easier way.

function

Returns an expression converted to the supplied data type.

syntax

CONVERT ( data type, expression [ , format-style ] )

parameters

The data type to which the expression will be converted.

expression The expression to convert.

format

-style For converting strings to date or time data types and vice versa, format-style is the style code that describes the date format string to use. Values for the format-style parameter have the following meanings: Does not contain century (yy) Contains century (yy) Output

- 0 or 100 Mmm dd yy hh:nn:ss:sss AM (or PM)

1 101 mm/dd/yy[yy]

2 102 [yy]yy.mm.dd

3 103 dd/mm/yy[yy]

4 104 dd.mm.yy[yy]

5 105 dd-mm-yy[yy]

6 106 dd Mmm yy[yy]

7 107 Mmm dd, yy[yy]

8 108 hh:nn:ss

- 9 or 109 Mmm dd yy hh:nn:ss:sssAM (or PM)

10 110 mm-dd-yy[yy]

11 111 [yy]yy/mm/dd

12 112 [yy]yymmdd

13 113 dd Mmm yyy hh:nn:ss:sss (24-hour clock, European default + ms, 4-digit year)

14 114 hh:nn:ss:sss (24 hour clock)

20 120 y-mm-dd hh:nn:ss:sss (24-hour clock, ODBC specification, 4-digit year)

21 121 y-mm-dd hh:nn:ss.sss (24-hour clock, ODBC specification plus milliseconds, 4-digit year)

If format is not provided

-style parameter, style code 0 is used.

For a description of the style generated by each output symbol, such as Mmm, see DATE_FORMAT option

[compatibility]。

Standards and Compatibility

SQL

/92 Supplier extension.

SQL

/99 Supplier extension.

Sybase is compatible with Adaptive Server Enterprise.

see also

CAST function [data type conversion]

example

The following statements illustrate the use of formatting styles:

SELECT CONVERT( CHAR( 20 ), order_date, 104 )

FROM sales_orderorder_date

16.03.2000

20.03.2000

23.03.2000

25.03.2000

SELECT CONVERT( CHAR( 20 ), order_date, 7 )

FROM sales_orderorder_date

Mar 16, 00

Mar 20, 00

Mar 23, 00

Mar 25, 00

The following statement illustrates the conversion to integers and returns values

5：

SELECT CONVERT( integer, 5.2 )CONVERT

Explicitly converts an expression of one data type to another. Because some requirements often use different date formats. The following can be found in

Date formatting in SQL Server.

SQL Server supports data formats in Arabic style using the Kuwait algorithm.

In the table, the two columns on the left indicate that

datetime or smalldatetime The style value converted to character data. Add 100 to the style value to get a four-digit year (yy) that includes century digits.

without century digits (yy) with century digits (yy)

standard

input

/Output **

- 0 or 100 (*) Default mon dd yy hh:miAM (or PM)

1 101 United States mm/dd/yy

2 102 ANSI yy.mm.dd

3 103 UK/France dd/mm/yy

4 104 Germany dd.mm.yy

5 105 Italy dd-mm-yy

6 106 - dd mon yy

7 107 - mon dd, yy

8 108 - hh:mm:ss

- 9 or 109 (*) Default + ms mon dd yy hh:mi:ss:mmmAM (or PM)

10 110 United States mm-dd-yy

11 111 Japan yy/mm/dd

12 112 ISO yymmdd

- 13 or 113 (*) European default + ms dd mon yhh:mm:ss:mmm(24h)

14 114 - hh:mi:ss:mmm(24h)

- 20 or 120 (*) ODBC specification y-mm-dd hh:mm:ss[.fff]

- 21 or 121 (*) ODBC specification with milliseconds y-mm-dd hh:mm:ss[.fff]

- 126(***) ISO8601 y-mm-dd Thh:mm:ss:mmm (without spaces)

- 130* Kuwait dd mon yhh:mi:ss:mmmAM

- 131* Kuwait dd/mm/yy hh:mi:ss:mmmAM

* The default (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, 21 or 121) always returns the century digits (yy).

** Input when converting to datetime; output when converting to character data.

*** Used exclusively for XML. For conversions from datetime or smalldatetime to character data, the output format is as shown 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 uses the year of expiration

2049 explains the two-digit year. 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 2030 as the cutoff year. SQL Server provides a configuration option ("Two-digit cutoff year") to change the cutoff year used by SQL Server and to apply consistency to dates. However, the safest way is to specify a four-digit year.

from the

When smalldatetime is converted to character data, styles that contain seconds or milliseconds display zeros in those positions. When converting from datetime or smalldatetime values, you can truncate unwanted parts of the date by using the appropriate char or varchar data type length.

The following table shows the number of entries from

float or real The style value when converted to character data.

value output

0 (default) Maximum 6 digits. Use scientific notation as needed.

1 is always an 8-bit value. Always use scientific notation.

2 is always a 16-bit value. Always use scientific notation.

In the table below, the left column represents the number of people

The style value when money or smallmoney is converted to character data.

value output

0 (default) No commas separate every three digits to the left of the decimal point and two digits to the right of the decimal point, for example 4235.98.

1 Separate every three digits to the left of the decimal point with commas and two digits to the right of the decimal point, for example 3,510.92.

2 No comma separates every three digits to the left of the decimal point, and four digits to the right of the decimal point, such as 4235.9819.

use

CONVERT：

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

select CONVERT(varchar, getdate(), 120 )

2004-09-12 11:06:08

select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),\'-\',\'\'),\' \',\'\'),\':\',\'\')

20040912110608

select CONVERT(varchar(12) , getdate(), 111 )

2004/09/12

select CONVERT(varchar(12) , getdate(), 112 )

20040912

select CONVERT(varchar(12) , getdate(), 102 )

2004.09.12

select CONVERT(varchar(12) , getdate(), 101 )

09/12/2004

select CONVERT(varchar(12) , getdate(), 103 )

12/09/2004

select CONVERT(varchar(12) , getdate(), 104 )

12.09.2004

select CONVERT(varchar(12) , getdate(), 105 )

12-09-2004

select CONVERT(varchar(12) , getdate(), 106 )

12 09 2004

select CONVERT(varchar(12) , getdate(), 107 )

09 12, 2004

select CONVERT(varchar(12) , getdate(), 108 )

11:06:08

select CONVERT(varchar(12) , getdate(), 109 )

09 12 2004 1

select CONVERT(varchar(12) , getdate(), 110 )

09-12-2004

select CONVERT(varchar(12) , getdate(), 113 )

12 09 2004 1

select CONVERT(varchar(12) , getdate(), 114 )

11:06:08.177

About SQL Convert method how to use the answer to the question to share here, I hope the above content can be of some help to everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.