This is the standard format used for datetime columns in SQL Server and saves the details as YYYY-MM-DD HH:MI:SS. But if you need to display the datetime in a human readable format you will need to convert it using the CONVERT function. For example, to convert the column ‘DateOfBirth’ to dd-mm-yyyy format. CONVERT( VARCHAR, DateOfBirth. Format 101 equals “mm/dd/yyyy” and format 112 equals “yyyymmdd”, both of which are date-only formats without time. There are several methods to convert a DATETIME to a DATE in SQL Server. The fastest (and most readable in my opinion) is to use CONVERT. When using Oracle SQL, there are many ways to convert data types. Three of the most common data types are string (VARCHAR2), number, and date. Let’s take a look at how you can convert to these types. Converting to a Number in Oracle SQL. To convert a value to a number data type, there are two ways you can do it. CONVERT(varchar,GETDATE,114) 'hh:mm:ss:mmm' 3 SELECT TOP 1 GETDATE 'Default', 4 CONVERT(varchar,GETDATE,108) 'hh:mm:ss', 5 CONVERT(varchar,GETDATE,114) 'hh. I would convert it to a datetime first, then to the format that you want: declare @day varchar(10) set @day = '6/21/2013' select convert(varchar(10), cast(@day as datetime), 112); See SQL Fiddle with Demo.

This SQL Server tutorial explains how to use the CONVERT function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the CONVERT function converts an expression from one datatype to another datatype. If the conversion fails, the function will return an error. Otherwise, it will return the converted value.

Syntax

The syntax for the CONVERT function in SQL Server (Transact-SQL) is:

Parameters or Arguments

type

The datatype that you wish to convert expression to. It can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image.

length

Optional. The length of the resulting data type for char, varchar, nchar, nvarchar, binary and varbinary.

expression

The value to convert to another datatype.

style

Optional. The format used to convert between datatypes, such as a date format or string format. It can be one of the following values:

Converting datetime to character

Value (without century)	Value (with century)	Explanation
0	100	mon dd yyyy hh:miAM/PM (Default)
1	101	mm/dd/yyyy (US standard)
2	102	yy.mm.dd (ANSI standard)
3	103	dd/mm/yy (British/French standard)
4	104	dd.mm.yy (German standard)
5	105	dd-mm-yy (Italian standard)
6	106	dd mon yy
7	107	Mon dd, yy
8	108	hh:mi:ss
9	109	mon dd yyyy hh:mi:ss:mmmAM/PM
10	110	mm-dd-yy (USA standard)
11	111	yy/mm/dd (Japan standard)
12	112	yymmdd (ISO standard)
13	113	dd mon yyyy hh:mi:ss:mmm (Europe standard - 24 hour clock)
14	114	hh:mi:ss:mmm (24 hour clock)
20	120	yyyy-mm-dd hh:mi:ss (ODBC canonical - 24 hour clock)
21	121	yyyy-mm-dd hh:mi:ss:mmm (ODBC canonical - 24 hour clock)
126	yyyy-mm-ddThh:mi:ss:mmm (ISO8601 standard)
127	yyyy-mm-ddThh:mi:ss:mmmZ (ISO8601 standard)
130	dd mon yyyy hh:mi:ss:mmmAM/PM (Hijri standard)
131	dd/mm/yy hh:mi:ss:mmmAM/PM (Hijri standard)

Converting float to real

Convert Date 112

Value	Explanation
0	Maximum 6 digits (Default)
1	8 digits
2	16 digits

Converting money to character

Value	Explanation
0	No comma delimiters, 2 digits to the right of decimal (ie: 1234.56)
1	Comma delimiters, 2 digits to the right of decimal (ie: 1,234.56)
2	No comma delimiters, 4 digits to the right of decimal (ie: 1234.5678)

Note

• When casting from a float or numeric to an integer, the CONVERT function will truncate the result. For other conversions, the CONVERT function will round the result.
• See also the TRY_CONVERT, CAST, and TRY_CAST functions.

Applies To

The CONVERT function can be used in the following versions of SQL Server (Transact-SQL):

• SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example

Let's look at some SQL Server CONVERT function examples and explore how to use the CONVERT function in SQL Server (Transact-SQL).

For example:

Syntax

Sql Convert Date To 112

Description of the illustration 'convert.gif'

Sql Convert 112

Purpose

CONVERT converts a character string from one character set to another.

• The char argument is the value to be converted. It can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

• The dest_char_set argument is the name of the character set to which char is converted.

• The source_char_set argument is the name of the character set in which char is stored in the database. The default value is the database character set.

The return value for CHAR and VARCHAR2 is VARCHAR2. For NCHAR and NVARCHAR2, it is NVARCHAR2. For CLOB, it is CLOB, and for NCLOB, it is NCLOB.

Both the destination and source character set arguments can be either literals or columns containing the name of the character set.

For complete correspondence in character conversion, it is essential that the destination character set contains a representation of all the characters defined in the source character set. Where a character does not exist in the destination character set, a replacement character appears. Replacement characters can be defined as part of a character set definition.

Note:

Oracle discourages the use of the CONVERT function in the current Oracle Database release. The return value of CONVERT has a character data type, so it should be either in the database character set or in the national character set, depending on the data type. Any dest_char_set that is not one of these two character sets is unsupported. The char argument and the source_char_set have the same requirements. Therefore, the only practical use of the function is to correct data that has been stored in a wrong character set.

Values that are in neither the database nor the national character set should be processed and stored as RAW or BLOB. Procedures in the PL/SQL packages UTL_RAW and UTL_I18N—for example, UTL_RAW.CONVERT—allow limited processing of such values. Procedures accepting a RAW argument in the packages UTL_FILE, UTL_TCP, UTL_HTTP, and UTL_SMTP can be used to output the processed data.

Examples

The following example illustrates character set conversion by converting a Latin-1 string to ASCII. The result is the same as importing the same string from a WE8ISO8859P1 database to a US7ASCII database.

Common character sets include:

• US7ASCII: US 7-bit ASCII character set

• WE8ISO8859P1: ISO 8859-1 West European 8-bit character set

• EE8MSWIN1250: Microsoft Windows East European Code Page 1250

• WE8MSWIN1252: Microsoft Windows West European Code Page 1252

• WE8EBCDIC1047: IBM West European EBCDIC Code Page 1047

• JA16SJISTILDE: Japanese Shift-JIS Character Set, compatible with MS Code Page 932

• ZHT16MSWIN950: Microsoft Windows Traditional Chinese Code Page 950

• UTF8: Unicode 3.0 Universal character set CESU-8 encoding form

• AL32UTF8: Unicode 5.0 Universal character set UTF-8 encoding form

You can query the V$NLS_VALID_VALUES view to get a listing of valid character sets, as follows:

See Also:

Oracle Database Globalization Support Guide for information on supported character sets and Oracle Database Reference for information on the V$NLS_VALID_VALUES view