SQL Server/T-SQL Tutorial/Data Convert Functions/CAST
Содержание
- 1 Data type conversion can be performed using the CAST() and CONVERT() functions.
- 2 SELECT CAST("123.4" AS Decimal)
- 3 select CAST("2002-35:00" AS smalldatetime) + 1
- 4 select CAST("2002-35:00" AS smalldatetime) - 1 (Minus)
- 5 select CAST(CAST("2002-09-30" AS datetime) - CAST("2001-12-01" AS datetime) AS int)
- 6 The syntax of the CAST function: CAST(expression AS data_type)
- 7 Use cast function in dynamic sql
Data type conversion can be performed using the CAST() and CONVERT() functions.
<source lang="sql">
5> SELECT CAST("123" AS Int) 6> GO
123
(1 rows affected) 1> SELECT CAST("123.4" AS Decimal(9,2)) 2> GO
123.40
(1 rows affected) 1></source>
SELECT CAST("123.4" AS Decimal)
<source lang="sql">
5> 6> SELECT CAST("123.4" AS Decimal) 7> GO
123
(1 rows affected)</source>
select CAST("2002-35:00" AS smalldatetime) + 1
<source lang="sql">
2> 3> select CAST("2002-09-30 11:35:00" AS smalldatetime) + 1 4> GO
2002-10-01 11:35:00
(1 rows affected)</source>
select CAST("2002-35:00" AS smalldatetime) - 1 (Minus)
<source lang="sql">
2> 3> select CAST("2002-09-30 11:35:00" AS smalldatetime) - 1 4> GO
2002-09-29 11:35:00
(1 rows affected)</source>
select CAST(CAST("2002-09-30" AS datetime) - CAST("2001-12-01" AS datetime) AS int)
<source lang="sql">
2> 3> select CAST(CAST("2002-09-30" AS datetime) - CAST("2001-12-01" AS datetime) AS int) 4> GO
303
(1 rows affected) 1></source>
The syntax of the CAST function: CAST(expression AS data_type)
<source lang="sql">
A SELECT statement that uses the CAST function 7> 8> create table Billings ( 9> BankerID INTEGER, 10> BillingNumber INTEGER, 11> BillingDate datetime, 12> BillingTotal INTEGER, 13> TermsID INTEGER, 14> BillingDueDate datetime , 15> PaymentTotal INTEGER, 16> CreditTotal INTEGER 17> 18> ); 19> 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> create table Bankers( 4> BankerID Integer, 5> BankerName VARCHAR(20), 6> BankerContactLName VARCHAR(20), 7> BankerContactFName VARCHAR(20), 8> BankerCity VARCHAR(20), 9> BankerState VARCHAR(20), 10> BankerZipCode VARCHAR(20), 11> BankerPhone VARCHAR(20) 12> ) 13> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> SELECT BillingDate, BillingTotal, 4> CAST(BillingDate AS varchar) AS varcharDate, 5> CAST(BillingTotal AS int) AS integerTotal, 6> CAST(BillingTotal AS varchar) AS varcharTotal 7> FROM Billings 8> GO BillingDate BillingTotal varcharDate integerTotal varcharTotal
------------ ------------------------------ ------------ ------------------------------
2005-01-22 00:00:00.000 165 Jan 22 2005 12:00AM 165 165 2001-02-21 00:00:00.000 165 Feb 21 2001 12:00AM 165 165 2003-05-02 00:00:00.000 165 May 2 2003 12:00AM 165 165 1999-03-12 00:00:00.000 165 Mar 12 1999 12:00AM 165 165 2000-04-23 00:00:00.000 165 Apr 23 2000 12:00AM 165 165 2001-06-14 00:00:00.000 165 Jun 14 2001 12:00AM 165 165 2002-07-15 00:00:00.000 165 Jul 15 2002 12:00AM 165 165 2003-08-16 00:00:00.000 165 Aug 16 2003 12:00AM 165 165 2004-09-17 00:00:00.000 165 Sep 17 2004 12:00AM 165 165 2005-10-18 00:00:00.000 165 Oct 18 2005 12:00AM 165 165 (10 rows affected) 1> 2> drop table Billings; 3> drop table Bankers; 4> GO</source>
Use cast function in dynamic sql
<source lang="sql">
2> 3> CREATE TABLE Orders ( 4> OrderID int IDENTITY (1, 1) NOT NULL , 5> CustomerID nchar (5) NULL , 6> EmployeeID int NULL , 7> OrderDate datetime NULL , 8> RequiredDate datetime NULL , 9> ShippedDate datetime NULL , 10> ShipVia int NULL , 11> Freight money NULL DEFAULT (0), 12> ShipName nvarchar (40) NULL , 13> ShipAddress nvarchar (60) NULL , 14> ShipCity nvarchar (15) NULL , 15> ShipRegion nvarchar (15) NULL , 16> ShipPostalCode nvarchar (10) NULL , 17> ShipCountry nvarchar (15) NULL 18> ) 19> GO 1> 2> DECLARE @i AS INT; 3> SET @i = 10248; 4> 5> DECLARE @sql AS VARCHAR(52); 6> SET @sql = "SELECT * FROM dbo.Orders WHERE OrderID = " 7> + CAST(@i AS VARCHAR(10)) + N";"; 8> EXEC(@sql); 9> 10> GO OrderID CustomerID EmployeeID OrderDate RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress
ShipCity ShipRegion ShipPostalCode ShipCountry
---------- ----------- ----------------------- ----------------------- ----------------------- ----------- --------------------- ---------------------------------------- ------------------
--------------- --------------- -------------- ---------------
1> 2> drop table orders; 3> GO</source>
