SQL Server/T-SQL/Data Type/Convert
Содержание
- 1 Combine CONVERT() and DATEADD() to format
- 2 Conversion with CONVERT function for computated value with numbers in list
- 3 CONVERT() Function Styles
- 4 CONVERT function syntax: CONVERT(<target data type>, <expression to be converted>, <style>)
- 5 CONVERT(varchar,Date,101)
- 6 Format money (currency)
- 7 SELECT CONVERT(VarChar(20), "April")
- 8 SELECT CONVERT(VarChar(50), @Num, 2)
Combine CONVERT() and DATEADD() to format
3>
4> SELECT CONVERT(VarChar(20), DATEADD(m, -9, "9-8-1989"), 101)
5> GO
--------------------
12/08/1988
(1 rows affected)
Conversion with CONVERT function for computated value with numbers in list
7>
8>
9> create table Billings (
10> BankerID INTEGER,
11> BillingNumber INTEGER,
12> BillingDate datetime,
13> BillingTotal INTEGER,
14> TermsID INTEGER,
15> BillingDueDate datetime ,
16> PaymentTotal INTEGER,
17> CreditTotal INTEGER
18>
19> );
20> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2>
3> SELECT TOP 3 CONVERT(dec(10,2),BillingTotal + PaymentTotal)
4> FROM Billings
5> GO
------------
288.00
288.00
288.00
(3 rows affected)
1>
2>
3>
4>
5> drop table Billings;
6> GO
1>
CONVERT() Function Styles
Without Century With century 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 yyyy-mm-dd hh:mi:ss(24h)
21 or 121 ODBC with milliseconds yyyy-mm-dd hh:mi:ss.mmm(24h)
126 ISO8601 yyyy-mm-ddThh:mi:ss.mmm (no spaces)
130 Kuwaiti dd mon yyyyhh:mi:ss.mmmAM
131 Kuwaiti dd/mm/yyyy hh:mi:ss.mmmAM
CONVERT function syntax: CONVERT(<target data type>, <expression to be converted>, <style>)
5>
6>
7> SELECT CONVERT(datetime,(CONVERT(varchar,GETDATE(),112)))
8> GO
-----------------------
2008-08-17 00:00:00.000
(1 rows affected)
CONVERT(varchar,Date,101)
1> create table employee(
2> ID int,
3> name nvarchar (10),
4> salary int,
5> start_date datetime,
6> city nvarchar (10),
7> region char (1))
8> GO
1>
2> insert into employee (ID, name, salary, start_date, city, region)
3> values (1, "Jason", 40420, "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (2, "Robert",14420, "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (4, "Linda", 40620, "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (5, "David", 80026, "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (6, "James", 70060, "09/06/99", "Toronto", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (7, "Alison",90620, "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2> SELECT TOP 5 ID "Employee ID",
3> CONVERT(varchar,Start_Date,101) "Start date"
4> FROM Employee
5> GO
Employee ID Start date
----------- ------------------------------
1 02/01/1994
2 01/02/1995
3 12/03/1996
4 11/04/1997
5 10/05/1998
(5 rows affected)
1>
2>
3>
4>
5> drop table employee
6> GO
1>
Format money (currency)
1>
2> -- Use the CONVERT function.
3>
4>
5> create table employee(
6> ID int,
7> name nvarchar (10),
8> salary int,
9> start_date datetime,
10> city nvarchar (10),
11> region char (1))
12> GO
1>
2> insert into employee (ID, name, salary, start_date, city, region)
3> values (1, "Jason", 40420, "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (2, "Robert",14420, "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (4, "Linda", 40620, "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (5, "David", 80026, "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (6, "James", 70060, "09/06/99", "Toronto", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (7, "Alison",90620, "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2>
3> SELECT
4> ID,
5> Name,
6> "Salary $" +
7> CONVERT
8> (varchar(10), CONVERT(money, salary))
9> FROM
10> employee
11> GO
ID Name
----------- ---------- ------------------
1 Jason Salary $40420.00
2 Robert Salary $14420.00
3 Celia Salary $24020.00
4 Linda Salary $40620.00
5 David Salary $80026.00
6 James Salary $70060.00
7 Alison Salary $90620.00
8 Chris Salary $26020.00
9 Mary Salary $60020.00
(9 rows affected)
1>
2> drop table employee
3> GO
1>
2>
SELECT CONVERT(VarChar(20), "April")
8> SELECT CONVERT(VarChar(20), "April 29, 1988")
9> GO
--------------------
April 29, 1988
(1 rows affected)
1>
SELECT CONVERT(VarChar(50), @Num, 2)
4>
5>
6>
7> DECLARE @Num Float
8> SET @Num = 1234.56
9> SELECT CONVERT(VarChar(50), @Num, 2)
10> GO
--------------------------------------------------
1.234560000000000e+003
(1 rows affected)
1>