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.
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>
SELECT CAST("123.4" AS Decimal)
5>
6> SELECT CAST("123.4" AS Decimal)
7> GO
--------------------
123
(1 rows affected)
select CAST("2002-35:00" AS smalldatetime) + 1
2>
3> select CAST("2002-09-30 11:35:00" AS smalldatetime) + 1
4> GO
--------------------
2002-10-01 11:35:00
(1 rows affected)
select CAST("2002-35:00" AS smalldatetime) - 1 (Minus)
2>
3> select CAST("2002-09-30 11:35:00" AS smalldatetime) - 1
4> GO
--------------------
2002-09-29 11:35:00
(1 rows affected)
select CAST(CAST("2002-09-30" AS datetime) - CAST("2001-12-01" AS datetime) AS int)
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>
The syntax of the CAST function: CAST(expression AS data_type)
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
Use cast function in dynamic 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