SQL Server/T-SQL/Data Type/Convert

Материал из SQL эксперт
Перейти к: навигация, поиск

Combine CONVERT() and DATEADD() to format

   <source lang="sql">

3> 4> SELECT CONVERT(VarChar(20), DATEADD(m, -9, "9-8-1989"), 101) 5> GO

12/08/1988 (1 rows affected) 

</source>


Conversion with CONVERT function for computated value with numbers in list

   <source lang="sql">

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> 

</source>


CONVERT() Function Styles

   <source lang="sql">

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

</source>


CONVERT function syntax: CONVERT(<target data type>, <expression to be converted>, <style>)

   <source lang="sql">

5> 6> 7> SELECT CONVERT(datetime,(CONVERT(varchar,GETDATE(),112))) 8> GO

2008-08-17 00:00:00.000 (1 rows affected) 

</source>


CONVERT(varchar,Date,101)

   <source lang="sql">

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> 

      </source>


Format money (currency)

   <source lang="sql">

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> 

      </source>


SELECT CONVERT(VarChar(20), "April")

   <source lang="sql">

8> SELECT CONVERT(VarChar(20), "April 29, 1988") 9> GO

April 29, 1988 (1 rows affected) 1> 

</source>


SELECT CONVERT(VarChar(50), @Num, 2)

   <source lang="sql">

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> 

</source>
Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL/Data_Type/Convert&oldid=5813»