Format Function in Sql Server 2012

Finally, Microsoft introduced Format function in SQL Server 2012, that is very handy to format dates, times and currency in different cultures and custom format

In prior versions, we have to create our own function to format number.

Refer related article for prior versions : Format Number in SQL

In this post, I am sharing some examples of how to format date, time, currency and number with format function.

Format Date with culture

DECLARE @DATE DATE = GETDATE()
SELECT   FORMAT(@DATE, 'd', 'en-US') AS [Date (US)]
       , FORMAT(@DATE, 'd', 'en-IN') AS [Date (India)]
       , FORMAT(@DATE, 'd', 'en-GB') AS [Date (Great Britain)]
       , FORMAT(@DATE, 'd', 'de-DE') AS [Date (Denmark)]

RESULT

Format Date with custom format

DECLARE @DATE DATETIME = GETDATE()
SELECT    FORMAT(@DATE, 'dd/MM/yyyy') AS [Date (DD/MM/YYYY)]
        , FORMAT(@DATE, 'MM/dd/yyyy') AS [Date (MM/DD/YYYY)]
        , FORMAT(@DATE, 'MM/dd/yyyy ') AS [Date (MM/DD/YYYY)]
        , FORMAT(@DATE, 'MM/dd/yyyy hh:mm:ss tt') AS [Date (MM/DD/YYYY HH:MM:SS)]
        , FORMAT(@DATE, 'MM/dd/yyyy HH:mm:ss') AS [Date (MM/DD/YYYY H24:MM:SS)]
        , FORMAT(@DATE,'ddd') AS [Week Day]
        , FORMAT(@DATE,'MMM') AS [Short Month Name]
        , FORMAT(@DATE,'MMMM') AS [Full Month Name]
        , FORMAT(@DATE,'yyyy') AS [Year]

RESULT

Format Date with short codes

DECLARE @DATE DATETIME = GETDATE()
SELECT    FORMAT(@DATE,'d') AS [Short date pattern]
        , FORMAT(@DATE,'D') AS [Long Date pattern]       
        , FORMAT(@DATE,'t') AS [Short Time pattern]
        , FORMAT(@DATE,'T') AS [Long Time pattern]

RESULT

Format Currency with culture

DECLARE @Amount MONEY = 210525.52;   
SELECT    FORMAT(@Amount,'c','en-US') [Money (US)]
        , FORMAT(@Amount,'c','en-IN') [Money (India)]
        , FORMAT(@Amount,'c','en-GB') [Money (Great Britain)]
        , FORMAT(@Amount,'c','fr') [Money (France)]
        , FORMAT(@Amount,'c','de-DE') [Money (Denmark)]
        , FORMAT(@Amount,'c','ru-RU') [Money (Russia)]

RESULT

Format Percentage

DECLARE @Per DECIMAL(4,4) = 0.5545;
SELECT    FORMAT(@Per,'p0') [Percentage (Without decimal)]
        , FORMAT(@Per,'p1') [Percentage (With 1 decimal)]
        , FORMAT(@Per,'p2') [Percentage (With 2 decimal)]

RESULT

Format Number

DECLARE @num FLOAT=1234567.89
SELECT    FORMAT(@num,'N') AS 'Format Number Default'
        , FORMAT(@num,'#,#.00') AS 'Format Number with 2 decimal'
        , FORMAT(@num,'#,#.0') AS 'Format Number with 1 decimal'

RESULT

Get Numeric Value from String in SQL Server

In SQL Server, there are in-built functions like CharIndex, PatIndex to find the position of Alpha or Numeric or any special character value from the string. Replace and Stuff functions are there to replace some part of the string. But there are no-inbuilt functions to get only Numeric, Alpha or Alpha Numeric values from the string. In this Post, I am sharing User Defined Functions to get these using the in-built functions in SQL Server.

Function to get Only Numeric Values from the string

CREATE FUNCTION dbo.NumericOnly(
    @string VARCHAR(MAX)
)
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^0-9]%', @string)
    WHILE @intAlpha > 0
    BEGIN
        SET @string = STUFF(@string, @intAlpha, 1, '' )
        SET @intAlpha = PATINDEX('%[^0-9]%', @string)
    END
    RETURN     @string
END
GO
SELECT dbo.NumericOnly('abcd!@#ABCD#$%123%^%^')

Output

Function to get Only Alpha Values from the string

CREATE FUNCTION dbo.AlphaOnly(
    @string VARCHAR(MAX)
)
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^a-zA-Z]%', @string)
    WHILE @intAlpha > 0
    BEGIN
        SET @string = STUFF(@string, @intAlpha, 1, '' )
        SET @intAlpha = PATINDEX('%[^a-zA-Z]%', @string)
    END
    RETURN     @string
END
GO
SELECT dbo.AlphaOnly('abcd!@#ABCD#$%123%^%^')

Output

Function to get Only AlphaNumeric Values from the string

CREATE FUNCTION dbo.AlphaNumericOnly(
    @string VARCHAR(MAX)
)
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^a-zA-Z0-9]%', @string)
    WHILE @intAlpha > 0
    BEGIN
        SET @string = STUFF(@string, @intAlpha, 1, '' )
        SET @intAlpha = PATINDEX('%[^a-zA-Z0-9]%', @string)
    END
    RETURN     @string
END
GO
SELECT dbo.AlphaNumericOnly('abcd!@#ABCD#$%123%^%^')

Output

First character of string in SQL Server

In this post, we will see how to to fetch the first character of each word from the string and concatenate and return the new string

Example:

String: "How Are You?"

There are 3 Words in the string H-How, A-Are, Y=You? and you want to return HAY.

Just pass How Are You? to the function, it would return HAY.

CREATE FUNCTION dbo.FirstCharFromString(
    @string VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE @XML XML, @RESULT VARCHAR(MAX)
  SET @RESULT = ''
  SELECT  @XML = cast('<d>' + replace(@string, ' ', '</d><d>') + '</d>' as xml)
  SELECT  @RESULT = @RESULT + LEFT(T.split.value('.', 'NVARCHAR(MAX)'), 1)
  FROM    @XML.nodes('/d') T (split)
  RETURN  @RESULT
END

Now lets execute the function and check the result

SELECT dbo.FirstCharFromString('How Are You?')

OUTPUT

Find Nth Highest Salary in SQL Server

"Find nth Highest Salary?" is a very common question being asked in interviews.

There are no. of ways to find the nth highest value from the table.

In this post, I am sharing some of the ways to find out the same.

Lets first create a Salary table and populate some data in it.

create table tblSalary (salary INT)
insert into tblSalary
select 5000 union all
select 5000 union all
select 9000 union all
select 8000 union all
select 7000 union all
select 7000 union all
select 8000 union all
select 3000

In the table, highest value is 9000 and 2nd highest value is 8000 which is duplicate so 3rd distinct highest value is 7000.

Now lets find out 3rd highest salary with different ways

declare @nHighest int
set @nHighest = 3 --Set value here to get nth highest value

-- Method 1
select  min(salary) as salary
from (
  select  distinct top (@nHighest) salary as salary
  from  tblSalary order by salary desc
) tab

-- Method 2
select  top 1 (salary)
from (
  select  distinct top (@nHighest) salary as salary
  from  tblSalary order by salary desc
) tab order by salary

-- Method 3
select  distinct (a.salary)
from    tblSalary a
where    @nHighest = (select count (distinct (b.salary))
from    tblSalary b where a.salary<=b.salary)

-- Method 4
select  distinct salary
from (
  select  dense_rank() over (order by salary desc) as rnk, salary
  from  tblSalary
) tab where rnk = @nHighest

OUTPUT

New Date and Time Functions in SQL Server 2012

SQL Server 2012, code named Denali, introduced some new DATE and TIME Functions. In this post, we will discuss on how these new functions are used.

Here is the list of New DATE and TIME Functions

• DATEFROMPARTS
• TIMEFROMPARTS
• DATETIMEFROMPARTS
• DATETIME2FROMPARTS
• SMALLDATETIMEFROMPARTS
• DATETIMEOFFSETFROMPARTS
• EOMONTH

DATEFROMPARTS 
The DATEFROMPARTS function, returns a date value with the date part set to the specified year, specified month and the specified day, and the time portion set to the default as shown in the below query result.

DECLARE @YEAR  INT = 2012,
        @MONTH INT = 1,
        @DAY   INT = 1

SELECT DATEFROMPARTS (@YEAR, @MONTH, @DAY) AS [Result]

OUTPUT

TIMEFROMPARTS
The TIMEFROMPARTS function, returns a full time value as shown in the below query result.
It is important to note that the fractions argument actually depends on the precision argument.

For example:

• When fractions have a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second.
• When fractions have a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second.
• When fractions have a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

DECLARE @HOUR    INT = 11,
        @MINUTE  INT = 59,
        @SECONDS INT = 59
SELECT TIMEFROMPARTS (@HOUR, @MINUTE, @SECONDS, 500, 3) AS [Result]

OUTPUT

DATETIMEFROMPARTS
The DATETIMEFROMPARTS function,  returns a full datetime value as shown in the below query result.

DECLARE @YEAR         INT = 2012,
        @MONTH        INT = 1,
        @DAY          INT = 9,
        @HOUR         INT = 11,
        @MINUTE       INT = 59,
        @SECONDS      INT = 59,
        @MILLISECONDS INT = 0
SELECT DATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, @MILLISECONDS) AS [Result]

OUTPUT

DATETIME2FROMPARTS
The DATETIME2FROMPARTS function, returns a full datetime2 value as shown in the below query result.

DECLARE @YEAR    INT = 2012,
        @MONTH   INT = 1,
        @DAY     INT = 1,
        @HOUR    INT = 11,
        @MINUTE  INT = 59,
        @SECONDS INT = 59
SELECT DATETIME2FROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 500, 3) AS [Result]

OUTPUT

SMALLDATETIMEFROMPARTS
The SMALLDATETIMEFROMPARTS function, which is available in SQL Server 2012, returns a full smalldatetime value as shown in the below query result.

DECLARE @YEAR   INT = 2012,
        @MONTH  INT = 1,
        @DAY    INT = 1,
        @HOUR   INT = 11,
        @MINUTE INT = 59
SELECT SMALLDATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE) AS [Result]

OUTPUT

DATETIMEOFFSETFROMPARTS
The DATETIMEOFFSETFROMPARTS function, returns a full datetimeoffset data type as shown in the below query result. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.

DECLARE @YEAR    INT = 2012,
        @MONTH   INT = 1,
        @DAY     INT = 1,
        @HOUR    INT = 11,
        @MINUTE  INT = 59,
        @SECONDS INT = 59
SELECT DATETIMEOFFSETFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 500, 5, 30, 3) AS [Result]

OUTPUT

EOMONTH
The EOMONTH function, calculates the last date of the month based on the date which is passed as an input parameter.

DECLARE @STARTDATE DATETIME = GETDATE()
SELECT EOMONTH (@STARTDATE) AS [Last Date of Month]

OUTPUT

Convert XML to Table in SQL Server

In this post, I am sharing the script to read nodes of XML and display it in Tabular Form in SQL Server

XML Declaration

DECLARE @XML XML
SET @XML =
'<NewDataSet>
    <Employee>
       <EmpID>EMP001</EmpID>
       <FirstName>Sandeep</FirstName>
       <LastName>Mittal</LastName>
       <DOB>08/25/1981</DOB>
    </Employee>
    <Employee>
       <EmpID>EMP002</EmpID>
       <FirstName>Abhay</FirstName>
       <LastName>Kumar</LastName>
       <DOB>12/01/1982</DOB>
    </Employee>
</NewDataSet>'

Above declared XML is a collection of Employee details.

Now Let's write a query to fetch the nodes from the XML Variable and display the employee details in Tabular Form.

Query to get data in Table from XML Variable

SELECT EmpID = Node.Data.value('(EmpID)[1]', 'VARCHAR(MAX)')
        , [First Name] = Node.Data.value('(FirstName)[1]', 'VARCHAR(MAX)')
        , [Last Name] = Node.Data.value('(LastName)[1]', 'VARCHAR(MAX)')
        , DOB = Node.Data.value('(DOB)[1]', 'VARCHAR(MAX)')
FROM    @XML.nodes('/NewDataSet/Employee') Node(Data)

Output

Passing Datatable to Stored Procedure in SQL Server

In this article, we will learn how to pass DataTable from C# to a Stored Procedure in SQL Server.

Basic steps

• Create a Table Type with the structure as same of the DataTable in SQL Server
• Make this Table Type as Input Parameter of Stored Procedure
• Finally, pass the DataTable to the Table Type Parameter of the Stored Procedure

Note: This is supported on SQL Server 2008 onward.

Now let's take a step by step example

Step 1: Create a Table Employee

CREATE TABLE Employee(
    EmpID        VARCHAR(10)
    , EmpName    VARCHAR(50)
    , Gender     CHAR(1)
    , DOJ        DATETIME
)

Step 2: Create a Table Type

CREATE TYPE EmpTableType AS TABLE (
    EmpID        VARCHAR(10)
    , EmpName    VARCHAR(50)
    , Gender     CHAR(1)
    , DOJ        DATETIME
)

Step 3: Create a Stored Procedure that would take Table Type as parameter that we created in previous step.

CREATE PROCEDURE usp_GetEmpDetils(
    @EmpDet EmpTableType READONLY
) AS
BEGIN   
    INSERT INTO Employee
    SELECT * FROM @EmpDet
    SELECT * FROM Employee

END

Step 4: ADO.Net Code passing DataTable to the TableType Parameter in Stored Procedure

DataTable EmpTable = new DataTable();
EmpTable.Columns.Add("EmpID");
EmpTable.Columns.Add("EmpName");
EmpTable.Columns.Add("Gender");
EmpTable.Columns.Add("DOJ");
DataRow EmpRow = EmpTable.NewRow();
EmpRow["EmpID"] = "EMP0001";
EmpRow["EmpName"] = "Sandeep Mittal";
EmpRow["Gender"] = "M";
EmpRow["DOJ"] = "01/01/2010";       
EmpTable.Rows.Add(EmpRow);
EmpTable.AcceptChanges();

SqlConnection connection = new SqlConnection("data source=ServerName;database=DBName;uid=UserID;pwd=Password");
SqlCommand selectCommand = new SqlCommand("usp_GetEmpDetils", connection);
selectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = selectCommand.Parameters.AddWithValue("@EmpDet", EmpTable);
tvpParam.SqlDbType = SqlDbType.Structured;
connection.Open();
grid.DataSource = selectCommand.ExecuteReader();
grid.DataBind();
connection.Close();