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

For an instructor lead, in-depth look at learning SQL click below.

Learn SQL >>

When it comes to handling data in SQL, you may often encounter scenarios where the date information is stored as strings, and you want to convert them to the date format for further operations. SQL Server provides a built-in function called CAST to help you deal with such situations. Let’s jump into more particulars.

Introduction to CAST()

In SQL Server, the CAST() function is the simplest way of converting one data type to another. When it comes to converting a string to a date, you use this function to do the conversion, as shown below:

Using CAST() function to convert string to date

Let’s say we have a date string ‘2022-03-15’ that we want to convert to a datetime data type. Here’s how you would do it:

This statement would return ‘2022-03-15 00:00:00.000’ which includes time part, as the default DATETIME data type includes both date and time parts.

Introduction to CONVERT()

There’s another function provided by SQL Server, CONVERT() which gives you much more flexibility when dealing with date formats. CONVERT() function allows you to specify the style of the date string, meaning you can convert diverse date string formats to a date type.

Using CONVERT() function to convert string to date

Let’s take an example where we are given a date string ’15/03/2022′ and want to convert it to a date type. Here’s how you would use the CONVERT() function:

This statement will tell SQL Server that the input string is in the format ‘dd/mm/yyyy’ (as 103 is the code for this specific date format) and will convert it to the DATETIME datatype accordingly. The result will be ‘2022-03-15 00:00:00.000’.

In conclusion, depending on the complexity of your date strings, you might choose between CAST() and CONVERT() function to change date strings into date format in SQL Server. Make sure to apply these functions appropriately considering the data and format you are working with. Happy SQLing!