SQL Server/T-SQL Tutorial/Data Convert Functions/CONVERT
Содержание
- 1 Compare date type value after converting
- 2 Conversion failed when converting the varchar value "abc" to data type int.
- 3 CONVERT(char(10), GETDATE(), 112)
- 4 CONVERT(char(8), BillingDate, 1) and CONVERT(varchar(9), PaymentTotal, 1)
- 5 CONVERT(datetime, "20000704")
- 6 CONVERT() function requires two arguments: the first for the target data type and the second for the source value.
- 7 CONVERT function syntax
- 8 CONVERT(varchar(10), advance, 2)
- 9 CONVERT(varchar(12), OrderDate, 5)
- 10 CONVERT(varchar(20), GETDATE())
- 11 CONVERT(varchar,BillingDate,100) "Mmm dd yyyy hh:mmdp"
- 12 CONVERT(varchar,BillingDate,101) "mm/dd/yyyy"
- 13 CONVERT(varchar,BillingDate,103) "dd/mm/yyyy"
- 14 SELECT "ANSI Date: " + CONVERT(VarChar(50), GETDATE(), 102)
- 15 SELECT CONVERT(datetime, "10.12.99",1)
- 16 SELECT CONVERT(datetime, "10.12.99",4)
- 17 select CONVERT (INTEGER , "123")
- 18 SELECT CONVERT(VarChar(50), @Num, 0)
- 19 SELECT "Default Date: " + CONVERT(VarChar(50), GETDATE(), 100)
- 20 SELECT "German Date: " + CONVERT(VarChar(50), GETDATE(), 104)
- 21 SELECT "UK/French Date: " + CONVERT(VarChar(50), GETDATE(), 103)
- 22 SELECT "US Date: " + CONVERT(VarChar(50), GETDATE(), 101)
- 23 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
- 24 The syntax of the CONVERT function CONVERT(data_type, expression [, style])
- 25 Use convert function with variables
- 26 Values for the style argument of the CONVERT function when you convert a datetime expression to a character expression.
Compare date type value after converting
3>
4> CREATE TABLE Orders (
5> OrderID int NOT NULL ,
6> CustomerID nchar (5) NULL ,
7> EmployeeID int NULL ,
8> OrderDate datetime NULL ,
9> RequiredDate datetime NULL ,
10> ShippedDate datetime NULL ,
11> ShipVia int NULL ,
12> Freight money NULL DEFAULT (0),
13> ShipName nvarchar (40) NULL ,
14> ShipAddress nvarchar (60) NULL ,
15> ShipCity nvarchar (15) NULL ,
16> ShipRegion nvarchar (15) NULL ,
17> ShipPostalCode nvarchar (10) NULL ,
18> ShipCountry nvarchar (15) NULL)
19> GO
1>
2>
3> SELECT * FROM Orders WHERE CONVERT(varchar(12), OrderDate, 101) = CONVERT(varchar(12), GETDATE(), 101)
4> GO
OrderID CustomerID EmployeeID OrderDate RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress
ShipCity ShipRegion ShipPostalCode ShipCountry
----------- ---------- ----------- ----------------------- ----------------------- ----------------------- ----------- --------------------- ---------------------------------------- ------------------
------------------------------------------ --------------- --------------- -------------- ---------------
(0 rows affected)
1>
2> drop table Orders;
3> GO
Conversion failed when converting the varchar value "abc" to data type int.
4> SELECT CONVERT(INT, "abc")
5> GO
Msg 245, Level 16, State 1, Server J\SQLEXPRESS, Line 4
Conversion failed when converting the varchar value "abc" to data type int.
1> PRINT "This will print!"
2> GO
This will print!
1>
CONVERT(char(10), GETDATE(), 112)
3> CREATE TABLE my_date (Col1 datetime)
4> GO
1>
2> INSERT INTO my_date VALUES (CONVERT(char(10), GETDATE(), 112))
3> GO
(1 rows affected)
1>
2> drop table my_date;
3> GO
CONVERT(char(8), BillingDate, 1) and CONVERT(varchar(9), PaymentTotal, 1)
6> create table Billings (
7> BankerID INTEGER,
8> BillingNumber INTEGER,
9> BillingDate datetime,
10> BillingTotal INTEGER,
11> TermsID INTEGER,
12> BillingDueDate datetime ,
13> PaymentTotal INTEGER,
14> CreditTotal INTEGER
15>
16> );
17> 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>
2>
3> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
4> GO
(1 rows affected)
1>
2>
3>
4>
5>
6>
7> SELECT CONVERT(char(8), BillingDate, 1)
8> , CONVERT(varchar(9), PaymentTotal, 1)
9> FROM Billings
10> GO
-------- ---------
01/22/05 123
02/21/01 123
05/02/03 123
03/12/99 123
04/23/00 123
06/14/01 123
07/15/02 123
08/16/03 123
09/17/04 123
10/18/05 123
(10 rows affected)
1>
2>
3> drop table Billings;
4> GO
CONVERT(datetime, "20000704")
3> SET DATEFORMAT mdy
4> SELECT "FORMAT is mdy" = CONVERT(datetime, "20000704")
5> SET DATEFORMAT dmy
6> SELECT "FORMAT is mdy" = CONVERT(datetime, "20000704")
7> GO
FORMAT is mdy
-----------------------
2000-07-04 00:00:00.000
(1 rows affected)
FORMAT is mdy
-----------------------
2000-07-04 00:00:00.000
(1 rows affected)
CONVERT() function requires two arguments: the first for the target data type and the second for the source value.
3>
4> SELECT CONVERT(INT, "123")
5> SELECT CONVERT(Decimal(9,2), "123.4")
6> GO
-----------
123
(1 rows affected)
-----------
123.40
(1 rows affected)
CONVERT function syntax
CONVERT(data_ type(<length>), expression, <style>)
The length option is only used on the following data types: nchar, char, varchar, nvarchar, varbinary, and binary.
SQL Server interprets two digit years as follows:
if the year is less than 49 then the year is considered to be 20XX
while anything 50 and over is considered 19XX.
12> SELECT
13> CAST("11/11/72" as smalldatetime) AS "11/11/72",
14> CAST("6/5/40" as smalldatetime) as "6/5/40"
15> GO
11/11/72 6/5/40
-------------------- --------------------
1972-11-11 00:00:00 2040-06-05 00:00:00
(1 rows affected)
1> SELECT
2> CONVERT(varchar, getdate(), 1),
3> CONVERT(varchar, getdate(), 2),
4> CONVERT(varchar, getdate(), 3),
5> CONVERT(varchar, getdate(), 4),
6> CONVERT(varchar, getdate(), 5),
7> CONVERT(varchar, getdate(), 6)
8> GO
------------------------------ ------------------------------ ------------------------------ ---------------------------
--- ------------------------------ ------------------------------
10/21/07 07.10.21 21/10/07 21.10.07
21-10-07 21 Oct 07
(1 rows affected)
CONVERT(varchar(10), advance, 2)
2> CREATE TABLE titles(
3> title_id varchar(20),
4> title varchar(80) NOT NULL,
5> type char(12) NOT NULL,
6> pub_id char(4) NULL,
7> price money NULL,
8> advance money NULL,
9> royalty int NULL,
10> ytd_sales int NULL,
11> notes varchar(200) NULL,
12> pubdate datetime NOT NULL
13> )
14> GO
1>
2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> 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>
2> SELECT "Money" = advance,
3> "Varchar" = CONVERT(varchar(10), advance),
4> "Varchar-1" = CONVERT(varchar(10), advance, 1),
5> "Varchar-2" = CONVERT(varchar(10), advance, 2)
6> FROM titles
7> GO
Money Varchar Varchar-1 Varchar-2
--------------------- ---------- ---------- ----------
8000.0000 8000.00 8,000.00 8000.0000
5000.0000 5000.00 5,000.00 5000.0000
4000.0000 4000.00 4,000.00 4000.0000
2000.0000 2000.00 2,000.00 2000.0000
5000.0000 5000.00 5,000.00 5000.0000
0.0000 0.00 0.00 0.0000
8000.0000 8000.00 8,000.00 8000.0000
4000.0000 4000.00 4,000.00 4000.0000
(8 rows affected)
1>
2> drop table titles;
3> GO
CONVERT(varchar(12), OrderDate, 5)
3>
4> CREATE TABLE Orders (
5> OrderID int NOT NULL ,
6> CustomerID nchar (5) NULL ,
7> EmployeeID int NULL ,
8> OrderDate datetime NULL ,
9> RequiredDate datetime NULL ,
10> ShippedDate datetime NULL ,
11> ShipVia int NULL ,
12> Freight money NULL DEFAULT (0),
13> ShipName nvarchar (40) NULL ,
14> ShipAddress nvarchar (60) NULL ,
15> ShipCity nvarchar (15) NULL ,
16> ShipRegion nvarchar (15) NULL ,
17> ShipPostalCode nvarchar (10) NULL ,
18> ShipCountry nvarchar (15) NULL
19> )
20> GO
1> SELECT OrderDate, CONVERT(varchar(12), OrderDate, 5) AS "Converted"
2> FROM Orders
3> WHERE OrderID = 11050
4>
5> GO
OrderDate Converted
----------------------- ------------
(0 rows affected)
1>
2> drop table orders;
3> GO
1>
CONVERT(varchar(20), GETDATE())
3> SELECT
4> "returned as date"=GETDATE(),
5> "returned as string"=CONVERT(varchar(20), GETDATE())
6> GO
returned as date returned as string
----------------------- --------------------
2008-08-17 13:20:33.890 Aug 17 2008 1:20PM
(1 rows affected)
1>
2>
CONVERT(varchar,BillingDate,100) "Mmm dd yyyy hh:mmdp"
2>
3>
4> create table Billings (
5> BankerID INTEGER,
6> BillingNumber INTEGER,
7> BillingDate datetime,
8> BillingTotal INTEGER,
9> TermsID INTEGER,
10> BillingDueDate datetime ,
11> PaymentTotal INTEGER,
12> CreditTotal INTEGER
13>
14> );
15> 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> SELECT CONVERT(varchar,BillingDate,100) "Mmm dd yyyy hh:mmdp" FROM Billings
4> GO
Mmm dd yyyy hh:mmdp
------------------------------
Jan 22 2005 12:00AM
Feb 21 2001 12:00AM
May 2 2003 12:00AM
Mar 12 1999 12:00AM
Apr 23 2000 12:00AM
Jun 14 2001 12:00AM
Jul 15 2002 12:00AM
Aug 16 2003 12:00AM
Sep 17 2004 12:00AM
Oct 18 2005 12:00AM
(10 rows affected)
1>
2> drop table Billings;
3> GO
CONVERT(varchar,BillingDate,101) "mm/dd/yyyy"
2>
3>
4> create table Billings (
5> BankerID INTEGER,
6> BillingNumber INTEGER,
7> BillingDate datetime,
8> BillingTotal INTEGER,
9> TermsID INTEGER,
10> BillingDueDate datetime ,
11> PaymentTotal INTEGER,
12> CreditTotal INTEGER
13>
14> );
15> 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> SELECT CONVERT(varchar,BillingDate,101) "mm/dd/yyyy" FROM Billings
4> GO
mm/dd/yyyy
------------------------------
01/22/2005
02/21/2001
05/02/2003
03/12/1999
04/23/2000
06/14/2001
07/15/2002
08/16/2003
09/17/2004
10/18/2005
(10 rows affected)
1> drop table Billings;
2> GO
CONVERT(varchar,BillingDate,103) "dd/mm/yyyy"
3> create table Billings (
4> BankerID INTEGER,
5> BillingNumber INTEGER,
6> BillingDate datetime,
7> BillingTotal INTEGER,
8> TermsID INTEGER,
9> BillingDueDate datetime ,
10> PaymentTotal INTEGER,
11> CreditTotal INTEGER
12>
13> );
14> 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> SELECT CONVERT(varchar,BillingDate,103) "dd/mm/yyyy" FROM Billings
4> GO
dd/mm/yyyy
------------------------------
22/01/2005
21/02/2001
02/05/2003
12/03/1999
23/04/2000
14/06/2001
15/07/2002
16/08/2003
17/09/2004
18/10/2005
(10 rows affected)
1>
2>
3> drop table Billings;
4> GO
SELECT "ANSI Date: " + CONVERT(VarChar(50), GETDATE(), 102)
3>
4> SELECT "ANSI Date: " + CONVERT(VarChar(50), GETDATE(), 102)
5> GO
-------------------------------------------------------------
ANSI Date: 2008.08.07
(1 rows affected)
SELECT CONVERT(datetime, "10.12.99",1)
4> SELECT CONVERT(datetime, "10.12.99",1)
5> GO
-----------------------
1999-10-12 00:00:00.000
(1 rows affected)
SELECT CONVERT(datetime, "10.12.99",4)
4> SELECT CONVERT(datetime, "10.12.99",4)
5> GO
-----------------------
1999-12-10 00:00:00.000
(1 rows affected)
select CONVERT (INTEGER , "123")
34>
35> --CONVERT(datatype[(length)],input_data[,style])
36>
37> select CONVERT (INTEGER , "123")
38> GO
-----------
123
(1 rows affected)
1>
SELECT CONVERT(VarChar(50), @Num, 0)
3>
4> DECLARE @Num Money
5> SET @Num = 1234.56
6> SELECT CONVERT(VarChar(50), @Num, 0)
7> GO
--------------------------------------------------
1234.56
(1 rows affected)
1>
SELECT "Default Date: " + CONVERT(VarChar(50), GETDATE(), 100)
4> SELECT "Default Date: " + CONVERT(VarChar(50), GETDATE(), 100)
5> GO
----------------------------------------------------------------
Default Date: Aug 7 2008 9:19PM
(1 rows affected)
SELECT "German Date: " + CONVERT(VarChar(50), GETDATE(), 104)
3>
4> SELECT "German Date: " + CONVERT(VarChar(50), GETDATE(), 104)
5> GO
---------------------------------------------------------------
German Date: 07.08.2008
(1 rows affected)
SELECT "UK/French Date: " + CONVERT(VarChar(50), GETDATE(), 103)
3>
4> SELECT "UK/French Date: " + CONVERT(VarChar(50), GETDATE(), 103)
5> GO
------------------------------------------------------------------
UK/French Date: 07/08/2008
(1 rows affected)
SELECT "US Date: " + CONVERT(VarChar(50), GETDATE(), 101)
5> SELECT "US Date: " + CONVERT(VarChar(50), GETDATE(), 101)
6> GO
-----------------------------------------------------------
US Date: 08/07/2008
(1 rows affected)
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
5> CREATE TABLE titles(
6> title_id varchar(20),
7> title varchar(80) NOT NULL,
8> type char(12) NOT NULL,
9> pub_id char(4) NULL,
10> price money NULL,
11> advance money NULL,
12> royalty int NULL,
13> ytd_sales int NULL,
14> notes varchar(200) NULL,
15> pubdate datetime NOT NULL
16> )
17> GO
1>
2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> 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>
2>
3> SELECT * FROM titles WHERE pubdate BETWEEN "8/1/96" and "8/31/08"
4> GO
Msg 242, Level 16, State 3, Server J\SQLEXPRESS, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
1>
2> drop table titles;
3> GO
The syntax of the CONVERT function CONVERT(data_type, expression [, style])
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
Use convert function with variables
3> DECLARE @thisdate datetime, @nextmonth datetime
4> SET @thisdate = GETDATE()
5> SET @nextmonth = DATEADD(m, 1, GETDATE())
6> SELECT CONVERT(varchar, @thisdate, 101) "Today",
7> CONVERT(varchar, @nextmonth, 101) "One month from today",
8> DATEDIFF(d, @thisdate, @nextmonth) "Days between"
9> GO
Today One month from today Days between
------------------------------ ------------------------------ ------------
08/17/2008 09/17/2008 31
(1 rows affected)
1>
Values for the style argument of the CONVERT function when you convert a datetime expression to a character expression.
Style Number Style Number Output Type Style
without Century (yy) with Century (yyyy)
- 0 or 100 Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yyyy
2 102 ANSI yyyy.mm.dd
3 103 British/French dd/mm/yyyy
4 104 German dd.mm.yyyy
5 105 Italian dd-mm-yyyy
6 106 - dd mon yyyy
7 107 - mon dd, yyyy
- 8 or 108 - hh:mm:ss
- 9 or 109 Default + mon dd yyyy hh:mi:ss:mmmAM
milliseconds (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 Europe default + dd mon yyyy hh:mi:ss:mmm (24h)
milliseconds
14 114 - hh:mi:ss:mmm (24h)
20 or 120 ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
21 or 121 ODBC canonical + yyyy-mm-dd hh:mi:ss.mmm(24h)
milliseconds