Feeds:
Posts
Comments

Posts Tagged ‘Format() Function in SQL Server 2012’

SQL SERVER – How to format values as a percentage

Posted in Solutions, SQL SERVER, tagged , , , , on August 4, 2013| 2 Comments »

In SQL Server, we usually come across a situation where we need to format values as a percentage. Usually, developers format the values as a percentage manually. But in this article, I will share a shortcut to format values as a percentage.
Before proceeding with the solution, I would like to create a sample table and data to demonstrate the formatting.

USE tempdb
GO
--DROP TABLE [dbo].[Student]
--GO
CREATE TABLE [dbo].[Student]
(
[Student ID] int,
[Student Name] [varchar](50) NOT NULL,
[Percentage] numeric(18,2)
)
GO
Insert into dbo.[Student] values (1,'Bob', 0.10)
Insert into dbo.[Student] values (2,'Alexander',0.50)
Insert into dbo.[Student] values (3,'Hosanna',0.85)
Insert into dbo.[Student] values (4,'William',0.11)
Insert into dbo.[Student] values (5,'Hulda',1.20)
Insert into dbo.[Student] values (6,'Jacoba',1.15)
GO

Solution 1 :
In this solution, we need to manually calculate the percentage and then format it. Given below is the script.

--This script is compatible with SQL Server 2000 and above.
USE tempdb
GO
SELECT [Student ID]
,[Student Name]
,[Percentage]
,CONVERT(VARCHAR(50),[Percentage]*100)+' %' AS [%] FROM dbo.Student
GO

Formatvalues1.1

Solution 2 :
In this solution, we will use a new function shipped with SQL Server 2012 namely Format. You just need to pass the values in this function and it will format values as a percentage (without any hurdles of calculations).
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
SELECT [Student ID]
,[Student Name]
,[Percentage]
,FORMAT([Percentage],'p') AS [%] FROM dbo.Student
GO

Formatvalues1.1

Read Full Post »

SQL SERVER – How to convert Gregorian dates to Hijri date with Formatting

Posted in Solutions, SQL SERVER, tagged , , , , , on May 8, 2013| 4 Comments »

How to convert Gregorian date format to simple Hijri date format? Doesn’t it sound a very simple question? Yes,  because in the earlier versions of SQL Server you could convert Gregorian dates to simple Hijri date format (dd mon yyyy hh:mi:ss:mmmAM). But if you need to convert Gregorian dates to any specific Hijri date format, then comes the problem. Basically, the earlier version of SQL Server supports very limited type of Hijri date formats but in SQL Server 2012, you can convert Gregorian dates to ANY Hijri date format using FORMAT function.

Let me convert it using different methods :

Method 1 : Using Convert Function

--This script is compatible with SQL Server 2005 and above.
--This Method can convert into very limited format of Hijri dates.
DECLARE @DateTime AS DATETIME
SET @DateTime=GETDATE()
SELECT @DateTime AS [Gregorian Date]
, CONVERT(VARCHAR(11),@DateTime,131) AS [Gregorian date to Hijri date]
GO
--OUTPUT

gregorian dates1.0

Method 2 : Using Format Function

--This script is compatible with SQL Server 2012 and above.
DECLARE @DateTime AS DATETIME
SET @DateTime=GETDATE()

SELECT @DateTime AS [Gregorian Date]
,FORMAT(@DateTime,'dd-MM-yyyy','ar')
AS [Gregorian date to Hijri date]

UNION ALL

SELECT @DateTime AS [Gregorian Date]
,FORMAT(@DateTime,'dd/MM/yyyy','ar')
AS [Gregorian date to Hijri date]

UNION ALL

SELECT @DateTime AS [Gregorian Date]
,FORMAT(@DateTime,'yyyy-MM-dd','ar')
AS [Gregorian date to Hijri date]

UNION ALL

SELECT @DateTime AS [Gregorian Date]
,FORMAT(@DateTime,'dddd/MMMM/yyyy','ar')
AS [Gregorian date to Hijri date]

UNION ALL

SELECT @DateTime AS [Gregorian Date]
,FORMAT(@DateTime,'dd-MM-yyyy','ar')
AS [Gregorian date to Hijri date]
GO
--OUTPUT

gregorian dates1.1

Let me know if you know any better way.

Read Full Post »

SQL Server 2012 – String Function – FORMAT

Posted in Functions, SQL SERVER, tagged , , , , , on September 19, 2012| 19 Comments »

Format function is one the important functions in SQL Server 2012. This function is very useful to format data in a specified format.

Today, we will discuss each and every aspect of this function. Also we will discuss the need/importance of this function in SQL server.

By using this function, we do not need to do a lot of scripting to format the data.

Let me explain its syntax, parameters, purpose and examples in detail.
Syntax :

FORMAT ( value, format [, culture ] )

Parameters :

@value : Data to be converted in other format. (Mandatory)
@format : To define a format in nvarchar datatype. (Mandatory)
@culture : To define a culture. By default, it picks up culture from session. (Optional)

Purpose :
This function converts value from one format to another format by using culture. If culture is not available it will pick up the culture from the session. It will return value either in nvarchar data type or NULL.

Let me explain this with simple examples.

Example-1 : FORMAT – DATE WITH DEFAULT CULTURE

DECLARE @Date AS datetime=Getdate()

Select @Date AS [DATE]
SELECT FORMAT (@Date, 'dd/MM/yyyy') as [Result (dd/MM/yyyy)]
SELECT FORMAT (@Date, 'MM/dd/yyyy') as [Result (MM/dd/yyyy)]
SELECT FORMAT (@Date, 'yyyy/MM/dd') as [Result (yyyy/MM/dd)]

Example-2 : FORMAT – TIME WITH DEFAULT CULTURE

DECLARE @TIME AS DATETIME=Getdate()

Select @TIME AS [TIME]
SELECT FORMAT (@TIME, 'h:mm:ss tt') as [Result (h:mm:ss tt)]
SELECT FORMAT (@TIME, 'hh:mm:ss tt') as [Result (hh:mm:ss tt)]
SELECT FORMAT (@TIME, 'h:mm:ss') as [Result (h:mm:ss)]
SELECT FORMAT (@TIME, 'hh:mm:ss') as [Result (hh:mm:ss)]

Example-3 : FORMAT – NUMBER WITH DEFAULT CULTURE

DECLARE @NUMBER AS NUMERIC(18,4)=12345.789

Select @NUMBER as [NUMBER]
SELECT FORMAT (@NUMBER, '####0.00') as [Result (####0.00)]
SELECT FORMAT (@NUMBER, '####0.000') as [Result (####0.000)]
SELECT FORMAT (@NUMBER, '##,##0.00') as [Result (##,##0.00)]
SELECT FORMAT (@NUMBER, '#') as [Result (#)]

Example-4 : FORMAT – CURRENCY WITH DEFAULT CULTURE

DECLARE @CURRENCY MONEY = '24500.50';
SELECT FORMAT (@CURRENCY, 'C') AS  [RESULT]

Example-5 : FORMAT – DATE & TIME WITH MULTIPLE CULTURE

DECLARE @Date AS datetime=Getdate()

Select @Date as [DATE]
SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','en-US')
AS [English Culture]

SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','de-DE')
AS [German Culture]

SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','ja-JP')
AS [Japanese Culture]

SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','ar-SA')
AS [Arabic Culture]

SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','ur-PK')
AS [Urdu Culture]

Example-6 : FORMAT – CURRENCY WITH MULTIPLE CULTURE

DECLARE @CURRENCY MONEY = '24500.50';
SELECT FORMAT (@CURRENCY, 'C','en-US' ) AS [English Culture]
SELECT FORMAT (@CURRENCY, 'C','de-DE' ) AS [German Culture]
SELECT FORMAT (@CURRENCY, 'C','ja-JP' ) AS [Japanese Culture]
SELECT FORMAT (@CURRENCY, 'C','ar-SA' ) AS [Arabic Culture]
SELECT FORMAT (@CURRENCY, 'C', 'ur-PK' ) AS [Urdu Culture]

Example-7 : FORMAT – VALUE BY USING LANGUAGE INSTEAD OF CULTURE

SET LANGUAGE 'ENGLISH'

SELECT FORMAT (245000, 'C') AS [Currency]
SELECT FORMAT (GETDATE(),'D') AS [DATE]
SELECT FORMAT (24500.282, '#,###,###0.00') AS [Currency]

Reference : MSDN

Read Full Post »