CONVERT function [Data type conversion]

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1510/html/iqrefbb/Convert.htm

 

Returns an expression converted to a supplied data type.

 

 

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

   For converting strings to date or time data types and vice versa, format-style is a style code number that describes the date format string to be used. Table 4-17 lists the meanings of the values of the format-style argument.

Table 4-17: CONVERT format style code output

Without century (yy)

With century (yyyy)

Output

-

0 or 100

mmm dd yyyy hh:nnAM (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 yyyy 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 yyyy hh:nn:ss:sss (24 hour clock, Europe default + milliseconds, 4-digit year)

14

114

hh:nn:ss (24 hour clock)

20

120

yyyy-mm-dd hh:nn:ss (24-hour clock, ODBC canonical, 4-digit year)

21

121

yyyy-mm-dd hh:nn:ss.sss (24 hour clock, ODBC canonical with milliseconds, 4-digit year)

-

365

yyyyjjj (as a string or integer, where jjj is the Julian day number from 1 to 366 within the year)

If no format-style argument is provided, style code 0 is used.

 

 

The following statements illustrate the use of format styles:

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

 

order_date

16.03.1993

20.03.1993

23.03.1993

25.03.1993

...

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

 

order_date

mar 16, 93

mar 20, 93

mar 23, 93

mar 25, 93

...

The following statements illustrate the use of the format style 365, which converts data of type DATE and DATETIME to and from either string or integer type data:

CREATE TABLE tab
   (date_col DATE, int_col INT, char7_col CHAR(7));
INSERT INTO tab (date_col, int_col, char7_col)
   VALUES (‘Dec 17, 2004’, 2004352, ‘2004352’);

SELECT CONVERT(VARCHAR(8), tab.date_col, 365) FROM tab;returns ‘2004352’

SELECT CONVERT(INT, tab.date_col, 365) from tab;returns 2004352

SELECT CONVERT(DATE, tab.int_col, 365) FROM TAB;returns 2004-12-17

SELECT CONVERT(DATE, tab.char7_col, 365) FROM tab;returns 2004-12-17

The following statement illustrates conversion to an integer, and returns the value 5.

SELECT CONVERT( integer, 5.2 ) FROM iq_dummy

 

The result data type of a CONVERT function is a LONG VARCHAR. If you use CONVERT in a SELECT INTO statement, you must have a Large Objects Management option license or use CAST and set CONVERT to the correct data type and size.

See

“REPLACE function [String]” for more information.

 

 

 

 

 

“CAST function [Data type conversion]”

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值