Mixing Datatypes: CAST and CONVERT : Cast « Data Type « SQL Server / T-SQL






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>
           
       








Related examples in the same category

1.CAST(expression AS data type(<length>))
2.CAST (original_expression AS desired_datatype)
3.CAST(2.78128 AS integer)
4.CAST(2.78128 AS money)
5.CAST('11/11/72' as smalldatetime) AS '11/11/72'
6.select CAST('2002-09-30 11:35:00' AS smalldatetime) + 1 (plus)
7.select CAST('2002-09-30 11:35:00' AS smalldatetime) - 1 (minus)
8.select CAST(CAST('2002-09-30' AS datetime) - CAST('2001-12-01' AS datetime) AS int)
9.The CAST() function accepts one argument, an expression
10.Cast date type to char
11.CAST can still do date conversion
12.Cast date to different format
13.Cast number to varchar and use the regular expressions
14.Conversion with CONVERT function for computated value with numbers in list
15.Cast int to decimal
16.Convert varchar to number
17.CONVERT() does the same thing as the CAST() function
18.CAST(ID AS VarChar(5))
19.CAST('123.4' AS Decimal)
20.Arithmetic overflow error converting numeric to data type varchar.