SQL Server Convert String to Date + Examples

In this SQL Server tutorial, we will discuss How to convert a String to Date in SQL Server, Different SQL Server Conversion Scenarios, and cover the below topic.

• SQL Server Convert String to Date
• SQL Server Convert String to Date dd/mm/yyyy
• SQL Server Convert String to Date yyyymmdd
• SQL Server Convert String to Date mm/dd/yyyy
• SQL Server Convert String to Date with the format
• SQL Server Convert String to Date YYYYMM
• SQL Server Convert String to Date in where clause
• SQL Server Convert String to Date YYYYMMSSHHMMSS

All the examples, I have done here in sql server 2019 and SQL server management studio.

SQL Server Convert String to Date

When working with SQL Server, you may come across date values that are saved as strings. It’s critical to convert this information to a date data type because dates may be more useful during analysis.

In SQL Server, there are many conversion functions available through which we can easily convert an expression of one data type to another. And we are going to discuss each function in the section below.

Using Cast() to convert string to date

So Cast(), in SQL Server is a conversion function that is used to transform an expression from one data type to another. And it has the following syntax.

CAST( expression AS data_type [ ( length ) ] )  

The Cast() function accepts 2 arguments, the first is the expression that represents a valid value, and the second is the resulting data type that a user wants. Now to convert a string data type to date, we can execute the following query.

SELECT CAST('16 June 2021' AS date) AS Result;

In the above query, we are using the Cast() function within the SELECT statement. And in the Cast() function, we have given the input expression as ’16 June 2021′, and we also want the data type to be a date.

The will return the following output.

Read SQL Operand data type real is invalid for modulo operator

Using Convert() to convert string to date

The Convert() is a more advanced conversion function available in SQL Server, and it is much similar to Cast() function. The main difference between Covert() and Cast() functions is their syntax, and the Convert() also accepts an additional parameter named style.

CONVERT( data_type [ ( length ) ] , expression [ , style ] ) 

The style in the above syntax is an optional parameter used to define the resulting expression format. And we can easily convert any valid string data type to date using the following syntax.

SELECT CONVERT(date,'16 June 2021') AS Result;

In the above query, we are using the Convert() function within the SELECT statement. And in the Convert() function, we have given the input expression as ’16 June 2021′, and we also defined the data type to be a date. For this particular conversion, there is no need to specify the style parameter.

And the query will return the following result.

Using Parse() to convert string to date

The Parse() in SQL Server is another conversion function that converts the string data type to either numeric or Datetime data type. And the Parse() syntax is as follows.

PARSE( string_value AS data_type [ USING culture ] )

The Parse() function has 3 parameters, the first is an input string expression, the second is the required data type, and the third is an optional parameter named culture. The culture parameter is used to specify the formatting.

We can easily convert an input string to data using the following query.

SELECT PARSE('16 Jun 2021' as date) AS Result;

In the above query, we are using the arse() function within the SELECT statement. And in the Parse() function, we have given the input string as ’16 June 2021′, and we also defined the data type to be a date. For this particular conversion, there is no need to specify the culture parameter.

And the query will return the following result.

This is how to convert string to date sql server 2019.

SQL Server Convert String to Date dd/mm/yyyy

Now to convert a string to a dd/mm/yyyy formated date, first, we have to convert the string to a standard date format. And then, we can use the Convert() function to format the date according to our requirements.

For conversion, we can use the following query.

SELECT '16 Jun 2021' AS 'String',
	CONVERT(varchar(10), CAST('16 Jun 2021' as date), 103) AS [dd/mm/yyyy]

In the above query, we are using the Cast() function within the Convert() function to change the string to a date format. And then, we are using the formated date result within the Convert() function to get the desired result. To get the desired result, we are using the 103 style code.

In the end, the query will return the following output.

This is how to convert String to Date dd/mm/yyyy in sql server 2019.

Read: How to create functions in SQL Server Management Studio

SQL Server Convert String to Date yyyymmdd

Unfortunately, there is no direct way through which we can convert a string to yyyymmdd date format in SQL Server. For conversion, we have to first convert the string to a standard date format, and then we have to convert the standard date to a varchar data type.

And by using the Convert() function, we can format it to yyyymmdd format.

SELECT '16 Jun 2021' AS 'String',
	CONVERT(varchar(10), CAST('16 Jun 2021' as date), 112) AS [yyyymmdd]

In the query above, first, we are using the Cast() function within the Convert() function to change the string to a date format. And then, we are using the new date result within the Convert() function to get the required format result. To get the required result, we are using the 112 style code. In the end, it will return the following output.

This is how to convert String to Date yyyymmdd in SQL Server 2019.

Read: SQL Server Convert Datetime to date + Examples

SQL Server Convert String to Date mm/dd/yyyy

SELECT '16 Jun 2021' AS 'String',
	CONVERT(varchar(10), CAST('16 Jun 2021' as date), 101) AS [mm/dd/yyyy]

In the above query, we are using the Convert() function within the SELECT statement. And in the Convert() function, we are using the Cast() to first change the string data type to date, and then we are using the date expression to convert into varchar. And for the format, we are defining the style code in Covert() function as 101.

The query will return the following result.

This is how to convert String to Date mm/dd/yyyy in SQL Server 2019.

SQL Server Convert String to Date yyyymm

SELECT '16 Jun 2021' AS 'String',
	CONVERT(varchar(6), CAST('16 Jun 2021' as date), 112) AS [yyyymm]

In the query above, we are converting the string to a date data type by using the Cast() function. After this, we are using the Convert() function to change the date data type result to varchar of length 6. And in the Convert() function, we have defined the style parameter as 112 to get the date in yyyymmdd format.

After execution, the query will return the following result.

This is how to convert String to Date yyyymm in SQL Server 2019.

Read: SQL Server Convert Function + Examples

SQL Server Convert String to Date YYYYMMSSHHMMSS

In SQL Server, we cannot directly convert a string to a YYYYMMDDHHMMSS format. But we can convert it implicitly. For demonstration, consider the following query.

DECLARE @newdate datetime = CONVERT(Datetime, '2021-06-16 18:30:46.840', 120)

SELECT @newdate as String, CONVERT(CHAR(8), @newdate, 112) + 
	  REPLACE(CONVERT(CHAR(8), @newdate, 108), ':', '') AS [YYYYMMDDHHMMSS]

• In the query, first, we have declared a DateTime variable, and for the variable value, we are converting the input string to a DateTime value by using the Convert() function.
• After this, we are using the DateTime variable in the Convert() function to extract 2 parts. From the first Convert() function, we are extracting the date part (YYYYMMDD), and from the second Convert() function, we are extracting the timing part (HH:MM: SS).
• And then, we have used the replace function to remove “:” (colon) from the timing part.

After the execution it will return the following output.

This is how to convert String to Date YYYYMMSSHHMMSS in SQL Server 2019.

Read: Saving changes is not permitted in SQL Server

SQL Server Convert String to Date in where clause

In SQL Server, we can even convert a string column to date within a WHERE clause by using any of the available conversion functions. Let’s understand the implementation with the help of an example.

Now consider the following sample employee table in SQL Server.

So in the given employee table, we have a Joining Date Column which contains varchar values representing date and time. Now we will try to convert the joining date column from varchar to date within the WHERE clause by using the following query.

Select * from Sample WHERE CONVERT(Date,JoiningDate) > '2020-10-19'

In the above query, we are using the SELECT statement to select all the columns from the table. After this, we have put a WHERE clause, and in the WHERE clause, we are using the Convert() function to change the joining date column to date data type. In the last, we are using the comparison operator to filter out the result.

After execution, the query will return the following output.

This is how to convert String to Date in where clause in SQL Server 2019.

Read: How to execute function in SQL with parameters

SQL Server Convert String to Date with the different format

In SQL Server, we can only convert a specific type of string to a date data type. We cannot convert any given string expression to date. For conversion, the string should follow a valid format to be converted to date.

In SQL Server, converting a string to date using the functions is dependent on the language settings. Therefore, we can only convert ISO or supported formats based on the current language settings. And if we try to convert a non-supported string format, it will raise an error.

Suppose, the language in SQL Server instance is set to English(US), this means that the supported format for a string will be “mm/dd/yyyy”. And if we try some other format, it will give an error, as shown in the image below.

For an error-free conversion, we have to provide the supported format (mm/dd/yyyy) as shown below.

This is how to convert string to date with a different format in SQL Server 2019.

You may like the following SQL Server tutorials:

• SQL Server Datetime functions

In this SQL Server tutorial, we have learned How to convert a String to Date in SQL Server, Different SQL Server Conversion Scenarios, and cover the below topic.

• SQL Server Convert String to Date
• SQL Server Convert String to Date dd/mm/yyyy
• SQL Server Convert String to Date yyyymmdd
• SQL Server Convert String to Date mm/dd/yyyy
• SQL Server Convert String to Date with format
• SQL Server Convert String to Date yyyymm
• SQL Server Convert String to Date in where clause
• SQL Server Convert String to Date yyyymmddhhmmss