Skip to main content
TechNet
TRY_PARSE (Transact-SQL)
 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2012) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server. Use TRY_PARSE only for converting from string to date/time and number types.

Topic link icon  Transact-SQL Syntax Conventions

  
TRY_PARSE ( string_value AS data_type [ USING culture ] )  

string_value
nvarchar(4000) value representing the formatted value to parse into the specified data type.

string_value must be a valid representation of the requested data type, or TRY_PARSE returns null.

data_type
Literal representing the data type requested for the result.

culture
Optional string that identifies the culture in which string_value is formatted.

If the culture argument is not provided, the language of the current session is used. This language is set either implicitly or explicitly by using the SET LANGUAGE statement. culture accepts any culture supported by the .NET Framework; it is not limited to the languages explicitly supported by SQL Server. If the culture argument is not valid, PARSE raises an error.

Returns the result of the expression, translated to the requested data type, or null if the cast fails.

Use TRY_PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value.

TRY_PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR).

This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

More information about the data_type parameter

The values for the data_type parameter are restricted to the types shown in the following table, together with styles. The style information is provided to help determine what types of patterns are allowed. For more information on styles, see the .NET Framework documentation for the System.Globalization.NumberStyles and DateTimeStyles enumerations.

CategoryType.NET typeStyles used
NumericbigintInt64NumberStyles.Number
NumericintInt32NumberStyles.Number
NumericsmallintInt16NumberStyles.Number
NumerictinyintByteNumberStyles.Number
NumericdecimalDecimalNumberStyles.Number
NumericnumericDecimalNumberStyles.Number
NumericfloatDoubleNumberStyles.Float
NumericrealSingleNumberStyles.Float
NumericsmallmoneyDecimalNumberStyles.Currency
NumericmoneyDecimalNumberStyles.Currency
Date and TimedateDateTimeDateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal
Date and TimetimeTimeSpanDateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal
Date and TimedatetimeDateTimeDateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal
Date and TimesmalldatetimeDateTimeDateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal
Date and Timedatetime2DateTimeDateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal
Date and TimedatetimeoffsetDateTimeOffsetDateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal

More information about the culture parameter

The following table shows the mappings from SQL Server languages to .NET Framework cultures.

Full nameAliasLCIDSpecific culture
us_englishEnglish1033en-US
DeutschGerman1031de-DE
FrançaisFrench1036fr-FR
日本語Japanese1041ja-JP
DanskDanish1030da-DK
EspañolSpanish3082es-ES
ItalianoItalian1040it-IT
NederlandsDutch1043nl-NL
NorskNorwegian2068nn-NO
PortuguêsPortuguese2070pt-PT
SuomiFinnish1035fi
SvenskaSwedish1053sv-SE
češtinaCzech1029Cs-CZ
magyarHungarian1038Hu-HU
polskiPolish1045Pl-PL
românăRomanian1048Ro-RO
hrvatskiCroatian1050hr-HR
slovenčinaSlovak1051Sk-SK
slovenskiSlovenian1060Sl-SI
ελληνικάGreek1032El-GR
българскиBulgarian1026bg-BG
русскийRussian1049Ru-RU
TürkçeTurkish1055Tr-TR
BritishBritish English2057en-GB
eestiEstonian1061Et-EE
latviešuLatvian1062lv-LV
lietuviųLithuanian1063lt-LT
Português (Brasil)Brazilian1046pt-BR
繁體中文Traditional Chinese1028zh-TW
한국어Korean1042Ko-KR
简体中文Simplified Chinese2052zh-CN
ArabicArabic1025ar-SA
ไทยThai1054Th-TH

A. Simple example of TRY_PARSE

SELECT TRY_PARSE('Jabberwokkie' AS datetime2 USING 'en-US') AS Result;  

Here is the result set.

Result  
---------------  
NULL  
  
(1 row(s) affected)  

B. Detecting nulls with TRY_PARSE

SELECT  
    CASE WHEN TRY_PARSE('Aragorn' AS decimal USING 'sr-Latn-CS') IS NULL  
        THEN 'True'  
        ELSE 'False'  
END  
AS Result;  

Here is the result set.

Result  
---------------  
True  
  
(1 row(s) affected)  

C. Using IIF with TRY_PARSE and implicit culture setting

SET LANGUAGE English;  
SELECT IIF(TRY_PARSE('01/01/2011' AS datetime2) IS NULL, 'True', 'False') AS Result;  
  

Here is the result set.

Result  
---------------  
False  
  
(1 row(s) affected)  

PARSE (Transact-SQL)
Conversion Functions (Transact-SQL)
TRY_CONVERT (Transact-SQL)
CAST and CONVERT (Transact-SQL)