SQL Convert Examples for Dates, Integers, Strings and more (2024)

By: Joe Gavin |Updated: 2024-05-28 |Comments (1) | Related: > Functions System

Problem

A common task while working with data in Microsoft SQL Server is converting from onedata type to another. Most often, it's done to change the way data is presented,but sometimes it is needed to make sure the right data types are being used forcomparisons, joins, or sorting.

The SQL CONVERT function, which has long been part of the SQL language, and as thename implies, can be used to convert a value of one data type into a specified datatype with optional formatting attributes. CONVERT gives you the ability to format, whereasthe ISO Compliant CAST function does not.

Solution

We'll look at several examples of using the SQL CONVERT function to convertand optionally format date, datetime, string, and integer data types.

Here are some reasons you might use the CONVERT function:

• Display dates in a different format
• Display numbers in a different format
• Convert integers or dates to strings to concatenate with text data
• Change the data type for sorting purposes
• Align mismatched data types for comparisons or joins

In theory, the best solution is to always make sure you use the correct datatype when storing data in the SQL database. Sometimes this was not done when a tablewas created, so the CONVERT function can be useful to change data types. Also,note that when using a function, like CONVERT, in the WHERE clause or forjoining tales, SQL Server will need to perform the function on all of the data,so it negates the benefits of indexing and could impact performance. If you aresolely using this for SELECTing data and changing what the output looks like fora column, the CONVERT function is pretty fast for SQL queries or storedprocedures.

The following SQL Server CONVERT examples were run on SQL Server 2022 Developer Edition.

Basic CONVERT Syntax

The SQL Server CONVERT command can take three parameters:

• data_type - the target data type
• expression - what is being converted
• style - (optional) - this is used for different data formatting options(see list of styles at end of the article)

CONVERT(data_type(length), expression, style)

SQL CONVERT mm/dd/yyyy

This example creates a variable called @Date of datatype DATE, which is set toequal '2024-01-01'.

To display it in the form mm/dd/yyyy as 01/01/2024,we CONVERT the value in @Date to a VARCHAR(10) with a style of 101 to get the desiredoutput.

DECLARE @Date DATE = '2024-01-01' -- date valueSELECT CONVERT(VARCHAR(10),@Date,101) AS [MM/DD/YYYY];GO

SQL CONVERT Date to mm/dd/yy

The following example is the same as above, except it uses a style of 1 to convert thedefault format yyyy-mm-dd to mm/dd/yy.

DECLARE @Date DATE = '2024-01-01' -- current date exampleSELECT CONVERT(VARCHAR(10),@Date,1) AS [MM/DD/YY];GO

SQL CONVERT Datetime to Date

The style number is optional. This example removes the time by converting DATETIMEto DATE and retaining the default yyyy-mm-dd format.

DECLARE @DateAndTime DATETIME = '2024-01-01 08:00:00.000' -- alternatively GETDATE()SELECT CONVERT (DATE,@DateAndTime) AS [Date];GO

SQL CONVERT String to Date

This will convert the string '2024-01-01' to type DATE. There isno style parameter specified, so the default format, yyyy-mm-dd, is retained.

DECLARE @Date VARCHAR(10) = '2024-01-01' -- character stringSELECT CONVERT (DATE, @Date) AS [Date];GO

SQL CONVERT Date Format

Here, we're converting DATETIME to DATE and using style number 1 to displayit in the mm/dd/yy format.

DECLARE @DateAndTime DATETIME = '2024-01-01 08:00:00.000'SELECT CONVERT (VARCHAR,@DateAndTime,1) AS [Date];GO

SQL CONVERT Datetime

Style 22 is used to change the default 'yyyy-mm-dd hh:mm:ss' to amore easily read U.S. date format with a 12-hour clock in the form mm/dd/yy hh:mmam/pm.

DECLARE @DateAndTime DATETIME = '2024-01-01 08:00:000'SELECT CONVERT (VARCHAR,@DateAndTime,22) AS [DateAndTime];GO

SQL CONVERT to String

This converts DATETIME to a string of type VARCHAR(25) in the format mon dd yyyyhh:ss am/pm.

DECLARE @DateAndTime DATETIME = '2024-01-01 08:00'SELECT CONVERT(VARCHAR(25), @DateAndTime) AS [DateAndTime];GO

Convert as Decimal in SQL

We can convert more than dates and times with CONVERT.

Here, we'll convertthe integer 5 to a decimal value with a precision of 3 (total digits) and carryit out to two decimal places (x.xx).

DECLARE @Num INT = 5SELECT CONVERT(DECIMAL(3,2), @Num) AS [Decimal];GO

Convert the Date Format in SQL

This query will convert the default format yyyy-mm-dd to mon dd, yy using style7.

DECLARE @Date DATE = '2024-01-01'SELECT CONVERT(VARCHAR,@Date,7) AS [Month DD, YY];GO

SQL CONVERT INT to String

This converts the integer 5 to a string.

DECLARE @Num INT = 5SELECT CONVERT(VARCHAR(10), @Num) as [String];GO

Convert Date Format in SQL

Here, we can convert the default date format to dd mon yy with style 6.

DECLARE @Date DATE = '2024-01-01'SELECT CONVERT(VARCHAR,@Date,6) AS [DD Mon YY];GO

Convert Datetime in SQL Server to MM/DD/YY

Convert a datetime value to MM/DD/YY in SQL Server.

DECLARE @DateAndTime DATETIME = '2024-01-01 08:00:00.000'SELECT CONVERT(VARCHAR, @DateAndTime, 1) AS [MM/DD/YY];GO

Convert Integer to String in SQL

Below is an example to convert an integer to a string.

DECLARE @MyInt INT = 1SELECT CONVERT(VARCHAR, @MyInt) AS [Varchar];GO

Convert SQL String to INT

We saw how to convert an integer to a string. Now, we'll convert a stringto an integer.

DECLARE @MyString VARCHAR(10) = '123'SELECT CONVERT(INT, @MyString) AS [Integer];GO

SQL CONVERT Date to String

Here, the DATE type variable value is converted to a string.

DECLARE @Date DATE = '2024-01-01'SELECT CONVERT(VARCHAR, @Date) AS [String];GO

SQL CONVERT Datetime to String

We can do the same for a DATETIME variable type. The output is in the formatmonth dd yyyy hh:mm am/pm.

DECLARE @DateAndTime DATETIME = '2024-01-01 08:00:00.000'SELECT CONVERT(VARCHAR(30), @DateAndTime) AS [String];GO

Different Date Formats using CONVERT in SQL

A common use of the CONVERT function is to convert dates to different formatsusing a style code.

The following chart shows the style codes and descriptions that can be used toreformat date and time output.

Next Steps

So far, we've seen how to convert date data types (date, smalldatetime, etc.), and optionally convertthe output format, as well as convert integers, bigint, decimals and numeric data types. The followingare links to more tips and tutorials on SQL CONVERT primarily used for datefunctions:

• SQL Date Format Options with SQL CONVERT Function
• Convert Problematic Data in SQL Server
• SQL Convert Date to YYYYMMDD
• Different Ways to Convert a SQL INT Value Into a String Value
• Convert Implicit and the Related Performance Issues with SQL Server
• New Data Type Conversion Functions in SQL Server 2012
• Convert SQL Server DateTime Data Type to DateTimeOffset Data Type
• SQL Server Function to Convert Integer Date to Datetime Format
• Build a Cheat Sheet for SQL Server Date and Time Formats
• Handle Conversion Between Time Zones in SQL Server - Part 1
• Handle Conversion Between Time Zones in SQL Server - Part 2
• Handle Conversion Between Time Zones in SQL Server - Part 3

About the author

Article Last Updated: 2024-05-28