{"id":1109,"date":"2013-06-23T03:01:24","date_gmt":"2013-06-22T21:31:24","guid":{"rendered":"http:\/\/sqlhints.com\/?p=1109"},"modified":"2013-07-07T03:11:54","modified_gmt":"2013-07-06T21:41:54","slug":"format-string-function-in-sql-server-2012","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2013\/06\/23\/format-string-function-in-sql-server-2012\/","title":{"rendered":"FORMAT STRING FUNCTION IN SQL SERVER 2012"},"content":{"rendered":"

FORMAT<\/strong> is one of the new built-in String Function introduced as a Part of Sql Server 2012. It returns the value formatted in the specified format using the optional culture parameter value. It is not an Sql Server native function instead it is .NET CLR dependent function.<\/em><\/p>\n

SYNTAX:<\/strong> FORMAT ( value, format [, culture ] )<\/p>\n\n\n\n\n\n\n
Parameter<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
value:<\/strong><\/td>\nValue to be formatted<\/td>\n<\/tr>\n
format:<\/strong><\/td>\nThis parameter specifies the format in which the vlaue will be formatted.<\/td>\n<\/tr>\n
culture:<\/strong><\/td>\nThis parameter is optional, it specifies the culture in which the value is formatted. If it is not specified then the language of the current session is used.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

RETURNS: Return value type is nvarchar.<\/p>\n

Example 1: FORMAT DATE with Culture<\/strong><\/p>\n

DECLARE @date DATETIME = GETDATE() \r\nSELECT @date AS 'GETDATE()',\r\n       FORMAT( @date, 'd', 'en-US') AS 'DATE IN US Culture',\r\n       FORMAT( @date, 'd', 'en-IN') AS 'DATE IN INDIAN Culture',\r\n       FORMAT( @date, 'd', 'de-DE') AS 'DATE IN GERMAN Culture'<\/pre>\n
Result:<\/span><\/p>\n
\"FORMAT_FUNCTION_IN_SQL_SERVER_2012_1\"<\/a><\/p>\n
Example 2: FORMAT CURRENCY with Culture<\/strong><\/p>\n
DECLARE @Price INT = 40\r\nSELECT FORMAT(@Price,'c','en-US') \r\n         AS 'CURRENCY IN US Culture',       \r\n    FORMAT(@Price,'c','de-DE')\r\n         AS 'CURRENCY IN GERMAN Culture'<\/pre>\n
Result:<\/span><\/p>\n
\"FORMAT_FUNCTION_IN_SQL_SERVER_2012_2\"<\/a><\/p>\n
Example 3: FORMAT CURRENCY<\/strong><\/p>\n
DECLARE @Price DECIMAL(5,3) = 40.356\r\nSELECT FORMAT( @Price, 'C') AS 'Default',\r\n      FORMAT( @Price, 'C0') AS 'With 0 Decimal',\r\n       FORMAT( @Price, 'C1') AS 'With 1 Decimal',\r\n       FORMAT( @Price, 'C2') AS 'With 2 Decimal',\r\n       FORMAT( @Price, 'C3') AS 'With 3 Decimal'<\/pre>\n
Result:<\/span>
\n\"FORMAT_FUNCTION_IN_SQL_SERVER_2012_3\"<\/a><\/p>\n
Example 4: FORMAT PERCENTAGE<\/strong><\/p>\n
DECLARE @Percentage float = 0.35674\r\nSELECT FORMAT( @Percentage, 'P') AS '% Default',\r\n       FORMAT( @Percentage, 'P0') AS '% With 0 Decimal',\r\n       FORMAT( @Percentage, 'P1') AS '% with 1 Decimal',\r\n       FORMAT( @Percentage, 'P2') AS '% with 2 Decimal',\r\n       FORMAT( @Percentage, 'P3') AS '% with 3 Decimal'<\/pre>\n
Result:<\/span>
\n\"FORMAT_FUNCTION_IN_SQL_SERVER_2012_4\"<\/a><\/p>\n
Example 5: FORMAT NUMBER<\/strong><\/p>\n
DECLARE @Number AS DECIMAL(10,2) = 454545.389\r\nSELECT FORMAT( @Number, 'N','en-US') AS 'Number Format in US',\r\n    FORMAT( @Number, 'N','en-IN')  AS 'Number Format in INDIA'\r\n\r\nSELECT FORMAT( @Number, '#.0')     AS 'With 1 Decimal',\r\n    FORMAT( @Number, '#.00')    AS 'With 2 Decimal',\r\n    FORMAT( @Number, '#,##.00') AS 'With Comma and 2 Decimal',\r\n    FORMAT( @Number, '##.00')   AS 'Without Comma and 2 Decimal'<\/pre>\n
Result:<\/span>
\n\"FORMAT_FUNCTION_IN_SQL_SERVER_2012_5\"<\/a><\/p>\n
Example 6: CUSTOM DATE FORMATS<\/strong><\/p>\n
DECLARE @date DATETIME = GETDATE() \r\nSELECT @date AS 'GETDATE()',\r\n    FORMAT ( @date, 'dd\/MM\/yyyy') AS 'dd\/MM\/yyyy',\r\n    FORMAT ( @date, 'MM\/dd\/yyyy') AS 'MM\/dd\/yyyy',\r\n    FORMAT ( @date, 'yyyy\/MM\/dd') AS 'yyyy\/MM\/dd' \r\n\r\nSELECT \r\n FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','en-US')\r\n   AS 'US',\r\n FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','hi-IN')\r\n   AS 'Hindi',\r\n FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','kn-IN')\r\n  AS 'Kannada'<\/pre>\n
Result:<\/span><\/p>\n
\"FORMAT_FUNCTION_IN_SQL_SERVER_2012_6\"<\/a><\/p>\n
DECLARE @date DATETIME = GETDATE()\r\nSELECT FORMAT ( @date, 'dd', 'en-US' ) AS 'US',\r\n\tFORMAT ( @date, 'ddd', 'en-US' )   AS 'US',\r\n\tFORMAT ( @date, 'dddd', 'en-US' )  AS 'US',\r\n\tFORMAT ( @date, 'dddd', 'kn-IN' )  AS 'Kannada',\r\n\tFORMAT ( @date, 'dddd', 'hi-IN' )  AS 'Hindi'\r\n\r\nSELECT FORMAT ( @date, 'M', 'en-US' )  AS 'US',\r\n\tFORMAT ( @date, 'MM', 'en-US' )    AS 'US',\r\n\tFORMAT ( @date, 'MMM', 'en-US' )   AS 'US',\r\n\tFORMAT ( @date, 'MMMM', 'en-US' )  AS 'US',\r\n\tFORMAT ( @date, 'MMMM', 'kn-IN' )  AS 'Kannada',\r\n\tFORMAT ( @date, 'MMMM', 'hi-IN' )  AS 'Hindi'\r\n\r\nSELECT FORMAT ( @date, 'y', 'en-US' )   AS 'US',\r\n       FORMAT ( @date, 'y', 'kn-IN' )   AS 'Kannada',\r\n       FORMAT ( @date, 'y', 'hi-IN' )   AS 'Hindi',\r\n       FORMAT ( @date, 'yy', 'en-US' )  AS 'US',\r\n       FORMAT ( @date, 'yyy', 'en-US' ) AS 'US'<\/pre>\n
Result:<\/span><\/p>\n
\"FORMAT_FUNCTION_IN_SQL_SERVER_2012_7\"<\/a><\/p>\n
Example 7: Invalid Culture<\/strong><\/p>\n
DECLARE @date DATETIME = GETDATE()\r\nSELECT FORMAT(@date,'d','Test') AS 'Invalid Culture'<\/pre>\n
Result:<\/span>
\nMsg 9818, Level 16, State 1, Line 2
\nThe culture parameter ‘Test’ provided in the function call is not supported.<\/span><\/p>\n
You may like to read the below new built-in function’s introduced in Sql Server 2012:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n<\/table>\n","protected":false},"excerpt":{"rendered":"
FORMAT is one of the new built-in String Function introduced as a Part of Sql Server 2012. It returns the value formatted in the specified format using the optional culture parameter value. It is not an Sql Server native function instead it is .NET CLR dependent function. SYNTAX: FORMAT ( value, format [, culture ] … Continue reading FORMAT STRING FUNCTION IN SQL SERVER 2012<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[13,3,5,118],"tags":[119,120,35,986,55],"class_list":["post-1109","post","type-post","status-publish","format-standard","hentry","category-functions","category-sql-server","category-sql-server-2012-sql-server","category-string-functions-functions","tag-format","tag-format-string-function-in-sql-server-2012","tag-new-feature-in-sql-server-2012","tag-sql-server","tag-sql-server-2012"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-hT","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/1109","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/comments?post=1109"}],"version-history":[{"count":46,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/1109\/revisions"}],"predecessor-version":[{"id":1513,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/1109\/revisions\/1513"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=1109"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=1109"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=1109"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
New Built in Functions introduced in Sql Server<\/strong><\/td>\n
CONVERSION FUNCTIONS<\/strong><\/td>\n
PARSE<\/a><\/strong><\/td>\nTRY_PARSE<\/a> <\/strong><\/td>\n
TRY_CONVERT<\/a><\/strong><\/td>\n
STRING FUNCTIONS<\/strong><\/td>\n
CONCAT<\/a><\/strong><\/td>\nFORMAT<\/a><\/strong<\/td>\n
LOGICAL FUNCTIONS<\/strong><\/td>\n
CHOOSE<\/a><\/strong><\/td>\nIIF<\/a><\/strong<\/td>\n
DATE AND TIME FUNCTIONS<\/strong><\/td>\n
EOMONTH<\/a><\/strong><\/td>\n
DATEFROMPARTS<\/a><\/strong><\/td>\nDATETIMEFROMPARTS<\/a><\/strong<\/td>\n
SMALLDATETIMEFROMPARTS<\/a> <\/strong><\/td>\nDATETIME2FROMPARTS <\/strong<\/td>\n
TIMEFROMPARTS <\/strong><\/td>\nDATETIMEOFFSETFROMPARTS<\/strong<\/td>\n