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.
<source lang="sql">
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>
</source>
CAST("11/11/72" as smalldatetime) AS "11/11/72"
<source lang="sql">
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>
</source>
CAST("123.4" AS Decimal)
<source lang="sql">
1> SELECT CAST("123.4" AS Decimal) 2> 3> GO
123
(1 rows affected) 1> 2>
</source>
CAST(2.78128 AS integer)
<source lang="sql">
1> -- Cast 2.78128 as an integer: 2> 3> SELECT CAST(2.78128 AS integer) 4> GO
2
(1 rows affected) 1>
</source>
CAST(2.78128 AS money)
<source lang="sql">
1> SELECT CAST(2.78128 AS money) 2> GO
2.7813
(1 rows affected) 1>
</source>
CAST can still do date conversion
<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> -- 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>
</source>
Cast date to different format
<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 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>
</source>
Cast date type to char
<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 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>
</source>
CAST(expression AS data type(<length>))
<source lang="sql">
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)
</source>
CAST(ID AS VarChar(5))
<source lang="sql">
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>
</source>
Cast number to varchar and use the regular expressions
<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 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>
</source>
CAST (original_expression AS desired_datatype)
<source lang="sql">
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
</source>
CONVERT() does the same thing as the CAST() function
<source lang="sql">
1> -- CONVERT() does the same thing as the CAST() function. 2> 3> SELECT CONVERT(INT, "123") 4> 5> GO
123
(1 rows affected) 1>
</source>
Convert varchar to number
<source lang="sql">
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>
</source>
Mixing Datatypes: CAST and CONVERT
<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> --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>
</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("2002-35:00" AS smalldatetime) + 1 (plus)
<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(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 CAST() function accepts one argument, an expression
<source lang="sql">
1> -- The CAST() function accepts one argument, an expression 2> 3> SELECT CAST("123" AS Int) 4> 5> GO
123
(1 rows affected) 1>
</source>
