Introducing the FORMAT() Function in SQL Server

In SQL Server Denali, a new string function called FORMAT() has been introduced. This function allows developers to convert values to a specified format and return them as a string. One of the key advantages of this function is that it is locale-aware, meaning it can format dates and numbers according to the specified locale. This can greatly simplify formatting tasks and help developers avoid writing unnecessary code.

Let’s take a look at some examples to understand how the FORMAT() function works:

Example 1: Location Aware FORMAT function with Date

DECLARE @d DATETIME = '01/01/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS US_Result;
SELECT FORMAT ( @d, 'd', 'fr-FR' ) AS FR_Result;
SELECT FORMAT ( @d, 'd', 'de-DE' ) AS DE_Result;

Example 2: Location Aware FORMAT function with Currency

DECLARE @d INT = 500;
SELECT FORMAT ( @d, 'c', 'en-US' ) AS US_Result;
SELECT FORMAT ( @d, 'c', 'fr-FR' ) AS FR_Result;
SELECT FORMAT ( @d, 'c', 'de-DE' ) AS DE_Result;

Example 3: Various formats of Datetime

-- Day
SELECT FORMAT ( GETDATE(), 'd', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'dd', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'ddd', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'dddd', 'en-US' ) AS US_Result;

-- Minutes
SELECT FORMAT ( GETDATE(), 'm', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'mm', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'mmm', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'mmmm', 'en-US' ) AS US_Result;

-- Year
SELECT FORMAT ( GETDATE(), 'y', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'yy', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'yyy', 'en-US' ) AS US_Result;

Example 4: Various formats of Currency

DECLARE @var INT = 50
SELECT FORMAT(@var,'c') AS Currency;
SELECT FORMAT(@var,'c1') AS Currency;
SELECT FORMAT(@var,'c2') AS Currency;
SELECT FORMAT(@var,'c3') AS Currency;

Example 5: Various miscalculation formats

DECLARE @var INT = 50
SELECT FORMAT(@var,'p') AS Percentage;
SELECT FORMAT(@var,'e') AS Scientific;
SELECT FORMAT(@var,'x') AS Hexa;
SELECT FORMAT(@var,'x4') AS Hexa1;

Example 6: Format returning results in various languages

SELECT FORMAT (GETDATE(), N'dddd MMMM dd, yyyy', 'en-US') AS English_Result;
SELECT FORMAT (GETDATE(), N'dddd MMMM dd, yyyy', 'hi') AS Hindi_Result;
SELECT FORMAT (GETDATE(), N'dddd MMMM dd, yyyy', 'gu') AS Gujarati_Result;

The FORMAT() function can also be used with table columns. Here is a quick example:

Example 7: Format returning results based on table column

SELECT ModifiedDate,
FORMAT(ModifiedDate, N'dddd MMMM dd, yyyy','fr') Fr_ModifiedDate,
UnitPrice,
FORMAT(UnitPrice, 'c','fr') Fr_UnitPrice
FROM Sales.SalesOrderDetail

As you can see, the FORMAT() function is a powerful tool that simplifies formatting tasks in SQL Server Denali. It has made life easier for developers by providing a convenient way to format dates, numbers, and currencies according to different locales.

Click to rate this post!

[Total: 0 Average: 0]

Comprehensive 360 Degree Assessment

Data Replication

Performance Optimization

Data Security

Database Migration

Expert Consultation

Cloud Migration Made Easy

Considering a move to the cloud? Axial SQL brings you proven migration strategies to streamline your transition. Our expert team ensures a smooth, efficient shift, keeping your data safe and accessible. Start your journey to the cloud with confidence!

SQL Performance Optimization

Is your SQL running slower than expected? Don't let sluggish performance hinder your business. Our optimization experts at Axial SQL specialize in tuning your databases for peak performance. Speed up your SQL and supercharge your data processing today!

Database Stability Solutions

Tired of frequent database outages? Discover stability with Axial SQL! Our comprehensive analysis identifies and resolves your database vulnerabilities. Enhance reliability, reduce downtime, and keep your operations running smoothly with our expert guidance.

Expert Database Team Evaluation

Questioning your database team's efficiency? Let Axial SQL provide an expert, unbiased analysis. We assess your team's strategies and workflows, offering insights and improvements to boost productivity. Elevate your database management to new heights!

Data Security Assurance

Concerned about your database security? Axial SQL is here to fortify your data defenses. Our specialized security assessments identify potential risks and implement robust protections. Keep your sensitive data secure and your peace of mind intact with our expert services.

Published on