SQL Server/T-SQL Tutorial/Data Convert Functions/CONVERT

Compare date type value after converting

   <source lang="sql">

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</source>

Conversion failed when converting the varchar value "abc" to data type int.

   <source lang="sql">

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></source>

CONVERT(char(10), GETDATE(), 112)

   <source lang="sql">

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</source>

CONVERT(char(8), BillingDate, 1) and CONVERT(varchar(9), PaymentTotal, 1)

   <source lang="sql">

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</source>

CONVERT(datetime, "20000704")

   <source lang="sql">

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)</source>

CONVERT() function requires two arguments: the first for the target data type and the second for the source value.

   <source lang="sql">

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)</source>

CONVERT function syntax

   <source lang="sql">

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)</source>

CONVERT(varchar(10), advance, 2)

   <source lang="sql">

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</source>

CONVERT(varchar(12), OrderDate, 5)

   <source lang="sql">

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></source>

CONVERT(varchar(20), GETDATE())

   <source lang="sql">

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></source>

CONVERT(varchar,BillingDate,100) "Mmm dd yyyy hh:mmdp"

   <source lang="sql">

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</source>

CONVERT(varchar,BillingDate,101) "mm/dd/yyyy"

   <source lang="sql">

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</source>

CONVERT(varchar,BillingDate,103) "dd/mm/yyyy"

   <source lang="sql">

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</source>

SELECT "ANSI Date: " + CONVERT(VarChar(50), GETDATE(), 102)

   <source lang="sql">

3> 4> SELECT "ANSI Date: " + CONVERT(VarChar(50), GETDATE(), 102) 5> GO

---

ANSI Date: 2008.08.07 (1 rows affected)</source>

SELECT CONVERT(datetime, "10.12.99",1)

   <source lang="sql">

4> SELECT CONVERT(datetime, "10.12.99",1) 5> GO

---

1999-10-12 00:00:00.000 (1 rows affected)</source>

SELECT CONVERT(datetime, "10.12.99",4)

   <source lang="sql">

4> SELECT CONVERT(datetime, "10.12.99",4) 5> GO

---

1999-12-10 00:00:00.000 (1 rows affected)</source>

select CONVERT (INTEGER , "123")

   <source lang="sql">

34> 35> --CONVERT(datatype[(length)],input_data[,style]) 36> 37> select CONVERT (INTEGER , "123") 38> GO

---

       123

(1 rows affected) 1></source>

SELECT CONVERT(VarChar(50), @Num, 0)

   <source lang="sql">

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></source>

SELECT "Default Date: " + CONVERT(VarChar(50), GETDATE(), 100)

   <source lang="sql">

4> SELECT "Default Date: " + CONVERT(VarChar(50), GETDATE(), 100) 5> GO

---

Default Date: Aug 7 2008 9:19PM (1 rows affected)</source>

SELECT "German Date: " + CONVERT(VarChar(50), GETDATE(), 104)

   <source lang="sql">

3> 4> SELECT "German Date: " + CONVERT(VarChar(50), GETDATE(), 104) 5> GO

---

German Date: 07.08.2008 (1 rows affected)</source>

SELECT "UK/French Date: " + CONVERT(VarChar(50), GETDATE(), 103)

   <source lang="sql">

3> 4> SELECT "UK/French Date: " + CONVERT(VarChar(50), GETDATE(), 103) 5> GO

---

UK/French Date: 07/08/2008 (1 rows affected)</source>

SELECT "US Date: " + CONVERT(VarChar(50), GETDATE(), 101)

   <source lang="sql">

5> SELECT "US Date: " + CONVERT(VarChar(50), GETDATE(), 101) 6> GO

---

US Date: 08/07/2008 (1 rows affected)</source>

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

   <source lang="sql">

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</source>

The syntax of the CONVERT function CONVERT(data_type, expression [, style])

   <source lang="sql">

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</source>

Use convert function with variables

   <source lang="sql">

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></source>

Values for the style argument of the CONVERT function when you convert a datetime expression to a character expression.

   <source lang="sql">

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</source>