SQL Server 2012 Parse Function

Continuing my series on SQL Server 2012, today we will explore the PARSE() function introduced in SQL Server 2012. The PARSE function accepts an expression as input and returns the value in a specific datatype

Consider the following example

select parse('1/2/2012' as datetime)

Which returns 2012-01-02 00:00:00.000. But the following code

select parse('19/2/2012' as datetime)

will throw an error

The error occurs because the server's date setting (MM/DD/YYYY) is native to US English. The value 19/2/2012 throws error because 19 can not be converted to a month. So we need to specify explicitly that the date is in the DD/MM/YYY format by using a culture. For eg: In Germany, the native date format is DD/MM/YYYY and we can specify this in the PARSE function as shown below

select parse('19/2/2012' as datetime using 'de-DE')

Which returns the value 2012-02-19 00:00:00.000

Similarly we can use the culture option to convert values. We an also convert money values. Consider this example

select parse('789,23' as money)

The above returns 78923.00. As the culture is not specified, by default US-English culture option is used. In Europe, a comma is used to specify the decimal point, so 789,23 is equal to 789.23. In order to tell the server that this is the European format, we should use the Europe culture option as shown below

select parse('789,23' as money using 'fr-FR')

fr-FR is for FRANCE and the value returned is 789.23

This way we can effectively parse values according to the different culture options

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan