August 04, 2012

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

Did you like this post?
kick it on
subscribe via rss subscribe via e-mail
print this post follow me on twitter

About The Author




0 Responses to "SQL Server 2012 Parse Function"
