In SQL Server, the PARSE() function returns the result of an expression, translated to the requested data type.

Basically, it enables us to parse a string expression to the specified data type. It’s intended for converting string values to either date/time or number types.

The PARSE() function can be handy when attempting to convert with CAST() or CONVERT() fails. The PARSE() function is able to parse the expression, and this may result in certain values being converted that wouldn’t normally be able to be converted.

Syntax

The syntax goes like this:

PARSE ( string_value AS data_type [ USING culture ] )

The optional culture argument allows us to specify the culture in which string_value is formatted. If we don’t provide this argument, the language of the current session is used.

Example

Here’s an example of using PARSE() to parse a string as a date:

SELECT PARSE( 'Friday, 20 January 2023' AS date);

Result:

2023-01-20

Here’s one that parses a string as money:

SELECT PARSE( '$200' AS money);

Result:

200

We can use the sys.dm_exec_describe_first_result_set function to check the data type of the result:

SELECT system_type_name
FROM sys.dm_exec_describe_first_result_set(
    'SELECT PARSE( ''$200'' AS money)', 
    null, 
    0);

Result:

money

The culture Argument

We can use the culture argument to specify how the string value that we provide is formatted. This can be handy when passing a date that’s in a format specific to a certain locale.

Example:

SELECT PARSE( '20/01/2023' AS date USING 'en-GB');

Result:

2023-01-20

In this case, the string I provided represents a date in the British format (en-GB), and so the PARSE() function was able to work out what date it is.

Here’s what happens when I change the culture argument to en-US:

SELECT PARSE( '20/01/2023' AS date USING 'en-US');

Result:

Error converting string value '20/01/2023' into data type date using culture 'en-US'.

The error occurred because this culture assumes that the date is provided in mm/dd/yyyy format (because I specified the en-US culture), but months don’t go past 12. Therefore, the only thing it could do was throw an error, because it can’t create a date with the 20th month.

If you don’t want to receive an error, use TRY_PARSE() instead. The TRY_PARSE() function returns NULL instead of an error whenever a cast fails.

More Info

See PARSE() vs CAST() vs CONVERT() in SQL Server: What’s the Difference? if you’re trying to figure out which function to use.

Also see PARSE() vs TRY_PARSE() in SQL Server: What’s the Difference?.

And check out the Microsoft documentation for more information, including a list of locales that can be used with PARSE(), as well as the valid data types that we can convert to.