If you work with SQL Server, chances are you’ve used at least one of the CONVERT() or CAST() functions to convert from one data type to another. If you’ve ever encountered an error while trying to convert a string to a date/time data type, the PARSE() function could be what you need.

For example, if you have a string like say, Fri, 20 Jul 2018, the CONVERT() or CAST() functions will throw an error. But the PARSE() function will handle it without a problem.

The PARSE() function returns the result of an expression, translated to the requested data type in SQL Server. So you can use it to “translate” your string value into a date/time data type (such as date, datetime, datetime2, etc).

Syntax

Here’s the syntax for the PARSE() function:

PARSE ( string_value AS data_type [ USING culture ] )

Where string_value is the value you want to be parsed, data_type is the data type you want it parsed into, and culture is an optional argument that you can use to specify which language format to use when parsing the string.

Basic Example

Here’s a basic example to demonstrate usage.

SELECT PARSE('Friday, 20 July 2018' AS datetime2) 
AS 'Result';

Result:

+-----------------------------+
| Result                      |
|-----------------------------|
| 2018-07-20 00:00:00.0000000 |
+-----------------------------+

Remove the Time Part

In the previous example, we specified that the string should be parsed as a datetime2 data type. This data type includes the time component wth a high precision. If you don’t need the time component, you can always parse it as a date data type.

Here’s the result if we specify a date data type:

SELECT PARSE('Friday, 20 July 2018' AS date) 
AS 'Result';

Result:

+------------+
| Result     |
|------------|
| 2018-07-20 |
+------------+

Keep the Time Part, But with Less Precision

And if you do need the time, but with less precision, you can always use the smalldatetime data type:

SELECT PARSE('2:35:50pm, Friday, 20 July 2018' AS smalldatetime) 
AS 'Result';

Result:

+---------------------+
| Result              |
|---------------------|
| 2018-07-20 14:36:00 |
+---------------------+

But if that’s not precise enough, there’s always the datetime data type:

SELECT PARSE('2:35:50.5234pm, Friday, 20 July 2018' AS datetime) 
AS 'Result';

Result:

+-------------------------+
| Result                  |
|-------------------------|
| 2018-07-20 14:35:50.523 |
+-------------------------+

Variations of the Date Input

The PARSE() function can usually work out the date you’re trying to convert, as long as you provide it as a valid representation of the requested data type. Here are some examples of the date being provided in various styles:

SELECT 
    PARSE('Friday, 20 July 2018' AS date) AS 'Result 1',
    PARSE('Fri, 20 July 2018' AS date) AS 'Result 2',
    PARSE('Friday, July 20 2018' AS date) AS 'Result 3',
    PARSE('Fri 20 Jul 18' AS date) AS 'Result 4';

Result:

+------------+------------+------------+------------+
| Result 1   | Result 2   | Result 3   | Result 4   |
|------------+------------+------------+------------|
| 2018-07-20 | 2018-07-20 | 2018-07-20 | 2018-07-20 |
+------------+------------+------------+------------+

However, it’s not psychic:

SELECT PARSE('Next Friday' AS date) 
AS 'Result';

Result:

Error converting string value 'Next Friday' into data type date using culture ''. 

Add a Culture

You can add a third argument to specify which culture your string is formatted in. For example, if your date is 01/06/2018, this could either mean 6 January 2018 or 1 June 2018, depending on the culture being used.

Here’s an example of specifying the culture:

SELECT 
    PARSE('01/06/2018' AS date USING 'en-US') AS 'en-US',
    PARSE('01/06/2018' AS date USING 'en-GB') AS 'en-GB';

Result:

+------------+------------+
| en-US      | en-GB      |
|------------+------------|
| 2018-01-06 | 2018-06-01 |
+------------+------------+

If you don’t provide this argument, the language of the current session is used.

Providing the Wrong Weekday

The PARSE() function is pretty smart, in that, if you provide the wrong weekday, it will return an error.

So if we use the same date as the previous examples, but we change the weekday from Friday to Thursday, here’s what happens:

SELECT PARSE('Thursday, 20 July 2018' AS date) 
AS 'Result';

Result:

Error converting string value 'Thursday, 20 July 2018' into data type date using culture ''.