Friday, May 18, 2012

SQL Server 2012 : Format, Concat Function

Microsoft Introduced two new string functions Format, Concat

Format Function is used to change the format of the string or date or currency into specified format along with Culture.

Format ( Value, Format, [Culture])

Examples

select getdate() -- 2012-05-17 19:26:13.047
select format(getdate(),'d') -- 5/17/2012
select format(getdate(), 'd', 'en-US') -- 5/17/2012
select format(getdate(), 'd', 'en-IN') -- 17-05-2012

declare @amt money
set @amt = 120165.77
select @amt -- 120165.77
select format(@amt, 'c', 'en-US') -- $120,165.77
select format(@amt, 'c', 'en-IN') -- Rs. 1,20,165.77
select format(@amt, 'c', 'ru-RU') -- 120 165,77р.

Format Function Can be used for customize date format
select format(getdate(), 'dd-MM-yy') -- 170512
select format(getdate(), 'ddd') -- Fri
select format(getdate(), 'MMM') -- May
select format(getdate(), 'yyyy') --2012
select format(getdate(), 'dd-MMM-yy') -- 17-May-12
select format(getdate(), 'hh:mm:ss') -- 07:19:02
select format(getdate(), 'hh:mm:ss tt') --07:19:12 PM


select format(getdate(),'d') -- 5/17/2012 (Short Date)
select format(getdate(),'D') -- Thursday, May 17, 2012 (Long Date)
select format(getdate(),'t') -- 7:22 PM (Short Time)
select format(getdate(),'T') -- 7:22:40 PM (Long Time)

Concat  -- It is used for concatenating two strings.

Concat (string1, string2 .....)

select 'Kalyan ' + 'Akula'
select 'Kalyan - ID' + 32650   -- It returns error because of an integer.

select concat('Kalyan ','Akula')
select concat('Kalyan - ID ',32650)  -- Here it is possible.

2 comments:

  1. I think Kalyan meant to say, "select concat('Kalyan - ID ',32650)" to show that the integer without single quotes is successfully concatenated. With the single quotes the integer was already a string before the concat function received it.

    ReplyDelete
    Replies
    1. Thanks and corrected the above syntax.

      Delete