SQL Server Convert String to Date with examples

In this example I will explore how to leverage the data type and convert varchar using these functions. I will also demonstrate the variety of different formatting methods like:

• dd mm yyyy
• yyyy mm dd
• mm dd yyyy
• yyyy mm dd hh:mi:ss
• mon dd yyyy
• mm dd hh:mi:ss 24h
• dd mm yy
• dd mon yyyy
• mon dd yyyy hh:miam

SQL Server functions for converting a String to a Date

Using the convert function to convert varchar to datetime

Right now, you’re probably asking yourself: how to convert a string to datetime. Let’s dive in and start by looking at the convert function.

SELECT CONVERT(datetime, '1999-01-01')

Why 1999? Well isn’t it obvious, we need to party like it’s 1999 using the select convert function. The following will output the following result:

1999-01-01 00:00:00.000

This example uses the data type to convert a string to a datetime with sql server. The output is not super pretty, but we’ll get to that in another example shortly. Let’s now look at how to use the SQL server cast function to convert varchar to date.

Using the cast function to convert a string to a date

SELECT CAST('1999-01-01' as datetime)

The syntax is very similar with sql server between the select convert function and the select cast function. The cast function also outputs the same result without the nice formatting of a string such as dd mm yyyy.

How to get different date formats in SQL Server

Now that we know how to use sql server convert string to date let’s look at how to format a string to a date.

The SQL Server CONVERT function allows for an optional third parameter which is called the style or the format and it accepts all different formats such as the dd mm yyyy or yyyy mm dd, etc.

Here is the example to convert varchar to date using the select convert function:

SELECT CONVERT(varchar, '1999-01-01', 105) -- 'dd mm yyyy'
SELECT CONVERT(varchar, '1999-01-01', 101) -- 'mm dd yyyy'

The convert statements above output the following:

01 01 1999

01 01 1999

To get the date format type to another there is a subtle difference when using the select convert function. In this first examples the input to convert the first parameter was datetime whereas in this example it is varchar so it outputs as a string formatted with the correct data type.

Frequently asked questions:

How do I convert a string to a date in SQL?

You use the sql server CONVERT() function or the CAST() function.

How convert dd mm yyyy string to date in SQL Server?

Using the CONVERT() function the third parameter accepts an integer with the format you would like outputted.

What is cast Getdate () as date?

It will convert string to date instead of datetime data type.

Why we use convert function in SQL?

This is a quick and easy way to leverage the built-in sql server functions to convert to the data type you want.

Can SQL convert dates?

Yes, SQL server can convert string to dates with two different functions.

Can we convert varchar to date in SQL?

Yes, you can use the select convert function with SQL server.

Which function is used convert string into date format?

Based on the data type you can either use the convert function or cast function with sql server convert string to date.

How do I convert a string to a specific date?

The second parameter of the select convert function is a string that you can set to any date value you want to convert.

How do you convert a number into a date format?

When the data type is a number and you want to convert a number to date you would use the epoch value for the given date in question with sql server convert string to date.

How convert date to DD MMM YYYY in SQL?

The third parameter to sql server convert function is an integer so for dd mmm yyyy you would use: 106.

Published on Jul 27, 2022

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | convert | cast