Mega Code Archive
The syntax of the CONVERT function CONVERT(data_type, expression [, style])
<![CDATA[
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
]]>