Home > Denali, TSQL > SQL Server Denali– PARSE() and TRY_PARSE() conversion functions

SQL Server Denali– PARSE() and TRY_PARSE() conversion functions

SQL Server Denali introduced new conversion functions: PARSE() and TRY_PARSE().

PARSE() function

PARSE() function converts expression (string value) to date/time and number data types if conversion is possible. If conversion isn’t possible it raises an error.

-- Parse to datetime specifying culture in which string value is formatted
SELECT PARSE('Monday, 13 December 2010' AS datetime USING 'en-US') AS Result
SELECT PARSE('28. srpnja 2011. četvrtak' AS datetime USING 'hr-HR') AS Result

Result
———————–
2010-12-13 00:00:00.000
Result
———————–
2011-07-28 00:00:00.000

-- If the culture argument isn't provided, the language of current session is used.
SELECT PARSE('Monday, 13 December 2010' AS datetime) AS Result

Result
———————–
2010-12-13 00:00:00.000

-- Language for this session is English:
SELECT PARSE('28. srpnja 2011. četvrtak' AS datetime) AS Result

It raise an error:
Msg 9819, Level 16, State 1, Line 1
Error converting string value ’28. srpnja 2011. četvrtak’ into data type datetime using culture ”.

Language for session is set either implicitly or explicitly by using SET LANGUAGE statement.

SET LANGUAGE Croatian
SELECT PARSE('28. srpnja 2011. četvrtak' AS datetime) AS Result

Result
———————–
2011-07-28 00:00:00.000

-- Parse with currency symbol
SELECT PARSE('$345.56' AS money USING 'en-US') AS Result
SELECT PARSE('345,56 kn' AS money USING 'hr-HR') AS Result

Result
———————
345,56
Result
———————
345,56

-- Parse to numeric data type
SELECT PARSE('345,58' AS numeric(8,1) USING 'hr-HR') AS Result
SELECT PARSE('345,58' AS numeric(8,2) USING 'hr-HR') AS Result

Result
—————————————
345.6
Result
—————————————
345.58

TRY_PARSE() function

TRY_PARSE() function converts expression (string value) to date/time and number data types or return NULL if conversion isn’t possible.

SELECT TRY_PARSE('$345.56' AS money USING 'hr-HR') AS Result
SELECT TRY_PARSE('$345.56' AS money USING 'en-US') AS Result

First value returns NULL, since ‘$’ is not a valid currency symbol according to Croatian language.

Result
———————
NULL
Result
———————
345,56

DECLARE @Table table (Datum varchar(100))
INSERT INTO @Table
VALUES('Monday, 13 December 2010'),('28. srpnja 2011. četvrtak')
SELECT Datum FROM @Table
/*
Datum
-------------------------
Monday, 13 December 2010
28. srpnja 2011. četvrtak
*/

-- Convert to datetime with English culture
SELECT TRY_PARSE(Datum AS datetime USING 'en-US') AS Datum
FROM @Table

-- Second value returns NULL, since '28. srpnja 2011. četvrtak' is
-- not a valid datetime formatting according to us_english
/*
Datum
-----------------------
2010-12-13 00:00:00.000
NULL
*/

-- Convert to datetime with English or Croatian language
SELECT
CASE WHEN TRY_PARSE(Datum AS datetime USING 'en-US') IS NULL
THEN PARSE(Datum AS datetime USING 'hr-HR')
ELSE PARSE(Datum AS datetime USING 'en-US')
END AS Datum
FROM @Table

/*
Datum
-----------------------
2010-12-13 00:00:00.000
2011-07-28 00:00:00.000
*/

Advertisements
Categories: Denali, TSQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: