Follow @endyourif rss Facebook LinkedIn

SQL Server Convert String to Date with examples SQL Server Convert String to Date with examples

Published on Jul 27, 2022

There are two common functions to convert a string to a date in sql server: the CONVERT() function and CAST() function.

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.

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

About Jamie Munro

Welcome to my blog. I have been compiling articles since 2009! Below is a list of every post I've ever created. Some of my major focuses have been compiling the best source of jQuery tutorials on the Internet today. A recent focus of mine is now focusing on creating the best SQL Tutorials for Beginners, Intermediate and Advanced Users. This is a compiliation of all my experience about how to do common SQL commands to optimize for performance. As you might see from the links around here, I've also written and published 5 books. My first and last book are focused around ASP.NET MVC and Web API tutorials. The last book has a big focus on integrating Knockout.js as the MVVM for the front-end code. O'Reilly Media was so interested in Knockout.js that they actually commissioned me to write that book first as it is one of the only books dedicated entirely to this MVVM library. So if you're looking for the best Knockout.js tutorials, I've compiled the best list. My first book, even though it was self published, was on the PHP framework: CakePHP. I used this framework for years and many of my first ever exampes were all focused on CakePHP tutorials that provide so many fantastic PHP examples. There are so many Javascript tutorials out there, but I noticed that one of the things I work on most with Javascript is arrays. With this I've worked hard on compiling a big list of really advanced Javascript array examples. These array examples are amazing including how to group by, get distinct array elements and so many more. I've also dabbled a little bit with Node.js and during those experiments I definitely ran into some common errors like Can't set headers after they are sent, Solving No Access-Control-Allow-Origin with Node js and Express, and Uncaught ReferenceError: require is not defined. With these common errors I've compiled a list of Node.js tutorials that help solve these problems, but more importantly getting you started with the basics.