SQL Server/T-SQL/Data Type/Cast
Содержание
- 1 Arithmetic overflow error converting numeric to data type varchar.
- 2 CAST("11/11/72" as smalldatetime) AS "11/11/72"
- 3 CAST("123.4" AS Decimal)
- 4 CAST(2.78128 AS integer)
- 5 CAST(2.78128 AS money)
- 6 CAST can still do date conversion
- 7 Cast date to different format
- 8 Cast date type to char
- 9 CAST(expression AS data type(<length>))
- 10 CAST(ID AS VarChar(5))
- 11 Cast number to varchar and use the regular expressions
- 12 CAST (original_expression AS desired_datatype)
- 13 CONVERT() does the same thing as the CAST() function
- 14 Convert varchar to number
- 15 Mixing Datatypes: CAST and CONVERT
- 16 select CAST("2002-35:00" AS smalldatetime) - 1 (minus)
- 17 select CAST("2002-35:00" AS smalldatetime) + 1 (plus)
- 18 select CAST(CAST("2002-09-30" AS datetime) - CAST("2001-12-01" AS datetime) AS int)
- 19 The CAST() function accepts one argument, an expression
Arithmetic overflow error converting numeric to data type varchar.
8>
9> CREATE TABLE employee(
10> id INTEGER NOT NULL PRIMARY KEY,
11> first_name VARCHAR(10),
12> last_name VARCHAR(10),
13> salary DECIMAL(10,2),
14> start_Date DATETIME,
15> region VARCHAR(10),
16> city VARCHAR(20),
17> managerid INTEGER
18> );
19> GO
(1 rows affected)
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> SELECT CAST(ID AS VarChar(5)) + "-" + CAST(Salary AS VarChar(5))
4> FROM Employee
5>
6>
7> drop table employee;
8> GO
Msg 8115, Level 16, State 5, Server J\SQLEXPRESS, Line 3
Arithmetic overflow error converting numeric to data type varchar.
1>
2>
CAST("11/11/72" as smalldatetime) AS "11/11/72"
1> SELECT
2> CAST("11/11/72" as smalldatetime) AS "11/11/72",
3> CAST("6/5/40" as smalldatetime) as "6/5/40"
4> GO
11/11/72 6/5/40
-------------------- --------------------
1972-11-11 00:00:00 2040-06-05 00:00:00
(1 rows affected)
1>
CAST("123.4" AS Decimal)
1> SELECT CAST("123.4" AS Decimal)
2>
3> GO
--------------------
123
(1 rows affected)
1>
2>
CAST(2.78128 AS integer)
1> -- Cast 2.78128 as an integer:
2>
3> SELECT CAST(2.78128 AS integer)
4> GO
-----------
2
(1 rows affected)
1>
CAST(2.78128 AS money)
1> SELECT CAST(2.78128 AS money)
2> GO
---------------------
2.7813
(1 rows affected)
1>
CAST can still do date conversion
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> -- CAST can still do date conversion
3>
4> SELECT Start_Date, CONVERT(varchar(12), Start_Date, 111) AS "Converted" FROM Employee
5>
6>
7>
8> drop table employee
9> GO
Start_Date Converted
----------------------- ------------
1994-02-01 00:00:00.000 1994/02/01
1995-01-02 00:00:00.000 1995/01/02
1996-12-03 00:00:00.000 1996/12/03
1997-11-04 00:00:00.000 1997/11/04
1998-10-05 00:00:00.000 1998/10/05
1999-09-06 00:00:00.000 1999/09/06
2000-08-07 00:00:00.000 2000/08/07
2001-07-08 00:00:00.000 2001/07/08
2002-06-09 00:00:00.000 2002/06/09
(9 rows affected)
1>
Cast date to different format
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 Start_Date, CONVERT(varchar(12), Start_Date, 5) AS "Converted" FROM Employee
3> GO
Start_Date Converted
----------------------- ------------
1994-02-01 00:00:00.000 01-02-94
1995-01-02 00:00:00.000 02-01-95
1996-12-03 00:00:00.000 03-12-96
1997-11-04 00:00:00.000 04-11-97
1998-10-05 00:00:00.000 05-10-98
1999-09-06 00:00:00.000 06-09-99
2000-08-07 00:00:00.000 07-08-00
2001-07-08 00:00:00.000 08-07-01
2002-06-09 00:00:00.000 09-06-02
(9 rows affected)
1>
2> drop table employee
3> GO
1>
Cast date type to char
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 Start_Date, CAST(Start_Date AS varchar) AS "Converted" FROM Employee WHERE ID = 1
3> GO
Start_Date Converted
----------------------- ------------------------------
1994-02-01 00:00:00.000 Feb 1 1994 12:00AM
(1 rows affected)
1>
2> drop table employee
3> GO
1>
CAST(expression AS data type(<length>))
5> SELECT CAST(2.78128 AS integer)
6> SELECT CAST(2.78128 AS money)
7> GO
-----------
2
(1 rows affected)
---------------------
2.7813
(1 rows affected)
CAST(ID AS VarChar(5))
1>
2> create table employee(
3> ID int,
4> name nvarchar (10),
5> salary int,
6> start_date datetime,
7> city nvarchar (10),
8> region char (1))
9> 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 Name, CAST(ID AS VarChar(5))
3> FROM Employee
4> ORDER BY Name
5>
6>
7> drop table employee
8> GO
Name
---------- -----
Alison 7
Celia 3
Chris 8
David 5
James 6
Jason 1
Linda 4
Mary 9
Robert 2
(9 rows affected)
1>
Cast number to varchar and use the regular expressions
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 ID, Name
3> FROM Employee
4> WHERE CAST(Salary as varchar(10)) LIKE "%6%"
5> GO
ID Name
----------- ----------
4 Linda
5 David
6 James
7 Alison
8 Chris
9 Mary
(6 rows affected)
1>
2> drop table employee
3> GO
1>
CAST (original_expression AS desired_datatype)
6> CREATE TABLE employee
7> (
8> emp_id varchar(20),
9> fname varchar(20) NOT NULL,
10> minit char(1) NULL,
11> lname varchar(30) NOT NULL,
12> job_id smallint NOT NULL DEFAULT 1,
13> job_lvl tinyint DEFAULT 10,
14> pub_id char(4) NOT NULL DEFAULT ("9952"),
15> hire_date datetime NOT NULL DEFAULT (getdate())
16> )
17> GO
1>
2> insert employee values ("1", "Jack", "T", "Lee", 2, 215, "9952", "11/11/89")
3> insert employee values ("2", "Jode", "M", "Devon", 3, 200, "9952", "07/16/91")
4> insert employee values ("3", "Frac", "F", "Chang", 4, 227, "9952", "11/03/90")
5> insert employee values ("4", "Like", "A", "Lebihan", 5, 175, "0736", "06/03/90")
6> insert employee values ("5", "Paul", "X", "Henriot", 5, 159, "0877", "08/19/93")
7> insert employee values ("6", "Sick", "K", "Ottlieb", 5, 150, "1389", "04/05/91")
8> insert employee values ("7", "Rita", "B", "Muller", 5, 198, "1622", "10/09/93")
9> insert employee values ("8", "Mary", "J", "Pontes", 5, 246, "1756", "03/01/89")
10> insert employee values ("9", "Jane", "Y", "Labrune", 5, 172, "9901", "05/26/91")
11> insert employee values ("10","Carl", "F", "Hernadez",5, 211, "9999", "04/21/89")
12> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> SELECT lname + "-" + CAST(job_id AS varchar(2)) FROM employee
4> GO
---------------------------------
Lee-2
Devon-3
Chang-4
Lebihan-5
Henriot-5
Ottlieb-5
Muller-5
Pontes-5
Labrune-5
Hernadez-5
(10 rows affected)
1>
2> drop table employee;
3> GO
CONVERT() does the same thing as the CAST() function
1> -- CONVERT() does the same thing as the CAST() function.
2>
3> SELECT CONVERT(INT, "123")
4>
5> GO
-----------
123
(1 rows affected)
1>
Convert varchar to number
11>
12> DECLARE @Num Float
13> SET @Num = 1234.56
14> SELECT CONVERT(VarChar(50), @Num, 2)
15> GO
--------------------------------------------------
1.234560000000000e+003
(1 rows affected)
1>
2>
Mixing Datatypes: CAST and CONVERT
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> --Mixing Datatypes: CAST and CONVERT
3>
4> SELECT "Some text " + CAST(ID AS varchar) FROM Employee WHERE ID = 1
5> GO
----------------------------------------
Some text 1
(1 rows affected)
1>
2>
3>
4>
5> drop table employee
6> GO
1>
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("2002-35:00" AS smalldatetime) + 1 (plus)
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(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 CAST() function accepts one argument, an expression
1> -- The CAST() function accepts one argument, an expression
2>
3> SELECT CAST("123" AS Int)
4>
5> GO
-----------
123
(1 rows affected)
1>