Feeds:
Posts
Comments

Posts Tagged ‘SQL Server 2012 – String Function – FORMAT’

Recently, I came across a query where I needed to calculate the first and last day of the year. Fortunately, we do have the solution using DATEADD & DATEDIFF function. But the problem is we need to use these functions multiple times to achieve the first and last day of the year. So I went to the drawing board and started reducing the formula and finally I succeeded. Given below is the script to calculate the first and last day of the year using Format function (shipped with SQL Server 2012).

--This script is compatible with SQL Server 2012 and above.
SELECT DATEADD(YEAR,-1,FORMAT(Getdate(),'yyyy')) As [Result]
, 'First Day of Previous Year' As [Type]

UNION ALL
SELECT DATEADD(ms,-3,FORMAT(GETDATE(),'yyyy'))
, 'Last Day of Previous Year'

UNION ALL
SELECT FORMAT(GETDATE(),'yyyy')
, 'First Day of Current Year'

UNION ALL
SELECT DATEADD(ms,-3,FORMAT(DATEADD(YEAR,1,GETDATE()),'yyyy'))
, 'Last Day of Current Year'

UNION ALL
SELECT DATEADD(YEAR,1,FORMAT(Getdate(),'yyyy'))
, 'First Day of Previous Year'

UNION ALL
SELECT DATEADD(ms,-3,FORMAT(DATEADD(YEAR,2,GETDATE()),'yyyy'))
, 'Last Day of Previous Year'
--OUTPUT

first_day_and_last_day_of_year.1.1

Advertisements

Read Full Post »

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 »