The syntax of the CONVERT function CONVERT(data_type, expression [, style]) : CONVERT « Data Convert Functions « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Data Convert Functions
3. CONVERT

Code                       Output format
0 or 100 (default)         Mon dd yyyy hh:miAM/PM
1 or 101                   mm/dd/yy or mm/dd/yyyy
7 or 107                   Mon dd, yy or Mon dd, yyyy
8 or 108                   hh:mi:ss
10 or 110                  mm-dd-yy or mm-dd-yyyy
12 or 112                  yymmdd or yyyymmdd
14 or 114                  hh:mi:ss:mmm (24-hour clock)

Common style codes for converting real data to character data
Code                 Output
0 (default)          6 digits maximum
1   8 digits;        must use scientific notation
2   16 digits;       must use scientific notation

Common style codes for converting money data to character data
Code                Output
0 (default)         2 digits to the right of the decimal point; no commas to the left
1                   2 digits to the right of the decimal point; commas to the left
2                   4 digits to the right of the decimal point; no commas to the left


A SELECT statement that uses the CONVERT function

33>
34>
35> create table Billings (
36>     BankerID           INTEGER,
37>     BillingNumber      INTEGER,
38>     BillingDate        datetime,
39>     BillingTotal       INTEGER,
40>     TermsID            INTEGER,
41>     BillingDueDate     datetime ,
42>     PaymentTotal       INTEGER,
43>     CreditTotal        INTEGER
44>
45> );
46> 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>
4>
5> SELECT CONVERT(varchar, BillingDate) AS varcharDate,
6>     CONVERT(varchar, BillingDate, 1) AS varcharDate_1,
7>     CONVERT(varchar, BillingDate, 107) AS varcharDate_107,
8>     CONVERT(varchar, BillingTotal) AS varcharTotal,
9>     CONVERT(varchar, BillingTotal, 1) AS varcharTotal_1
10> FROM Billings
11> GO
varcharDate                    varcharDate_1                  varcharDate_107                varcharTotal                   varcharTotal_1
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
Jan 22 2005 12:00AM            01/22/05                       Jan 22, 2005                   165                            165
Feb 21 2001 12:00AM            02/21/01                       Feb 21, 2001                   165                            165
May  2 2003 12:00AM            05/02/03                       May 02, 2003                   165                            165
Mar 12 1999 12:00AM            03/12/99                       Mar 12, 1999                   165                            165
Apr 23 2000 12:00AM            04/23/00                       Apr 23, 2000                   165                            165
Jun 14 2001 12:00AM            06/14/01                       Jun 14, 2001                   165                            165
Jul 15 2002 12:00AM            07/15/02                       Jul 15, 2002                   165                            165
Aug 16 2003 12:00AM            08/16/03                       Aug 16, 2003                   165                            165
Sep 17 2004 12:00AM            09/17/04                       Sep 17, 2004                   165                            165
Oct 18 2005 12:00AM            10/18/05                       Oct 18, 2005                   165                            165

(10 rows affected)
1>
2>
3> drop table Billings;
4> GO