New tsql commands in sql server 2012 (part 5)

In this post i will write about the FORMAT, TRY_CONVERT, PARSE and the TRY_PARSE command.

FORMAT
With the format command it is possible to format a datetime with valid .NET Framework format string.

declare @datetime datetime = getdate();
declare @language nvarchar(5);

set @language = 'en-gb';

select format(@datetime, 'yyyy/MM/dd', @language) as result, @language [language];

set @language = 'de-de';

select format(@datetime, 'yyyy/MM/dd', @language) as result, @language [language];

This is the result despite of the .net format string (‘en-gb’ or ‘de-de’).

TRY_CONVERT
The TRY_CONVERT command does work as the CONVERT command, but if the conversion fails the TRY_CONVERT command does not throw an error, despite of it returns null.

SELECT try_convert(datetime, 'date', 101) as Date

This is the result, as you see the command returns null because it can not convert the string ‘date’ to an correct datetime.

PARSE and TRY_PARSE

In the following query i try to convert the german date string ‘Montag, 13 Dez 2010’ to datetime. With
the language string ‘de-DE’ it works fine but with the language string ‘en-GB’ the parse command produces an error.

declare @language nvarchar(5);

set @language = 'de-DE';

SELECT PARSE('Montag, 13 Dez 2010' AS datetime USING @language) AS Result

set @language = 'en-GB';

SELECT PARSE('Montag, 13 Dez 2010' AS datetime USING @language) AS Result

Result of the PARSE query:

If we try the same query with the TRY_PARSE command, the parsing with the english language string fails but try_parse reports no error, instead of it returns null.

declare @language nvarchar(5);

set @language = 'de-DE';

SELECT TRY_PARSE('Montag, 13 Dez 2010' AS datetime USING @language) AS Result

set @language = 'en-GB';

SELECT TRY_PARSE('Montag, 13 Dez 2010' AS datetime USING @language) AS Result

Result of the TRY_PARSE query:

Advertisements


If you have a note or a question please write a comment.

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