SQL SERVER – 2012 Functions – FORMAT() and CONCAT() – An Interesting Usage

Before continuing this blog post I would like to bring your attention to two of my earlier blog post where I have written in depth about FORMAT and CONCAT function.

Read the above two blog posts if you are interested in learning about the function in depth. Now recently I had need where I have to demonstrate a string on screen like as following:

Current Time is Sunday July 16, 2012

This was indeed not difficult for me to do in SQL Server 2012 as I wrote following comment very quickly.

SELECT 'Current Time is '+FORMAT (GETDATE(), N'dddd MMMM dd, yyyy', 'en-US') AS CurrentTimeString;

If you are using an earlier version than SQL Server 2012 above T-SQL script will not work and will give you an error.

However, while I was writing this I wondered if there is any other way to include the string along with the format. I tried various alteration and they are listed here. One of the interesting ones which came to my mind was about using the CONCAT function.

SELECT CONCAT('Current Time is ', FORMAT (GETDATE(), N'dddd MMMM dd, yyyy', 'en-US')) AS CurrentTimeString;

While I was trying to create a desired string, I came up with following T-SQL script. I can contact string without using CONCAT function and only with the format string.

SELECT FORMAT(GETDATE(), N'"Current Time is "dddd MMMM dd, yyyy', 'en-US') AS CurrentTimeString;

It is important to use double quote around the string in the FORMAT function, or it will give unexpected answer.

I have said it before many times but let me say it again – SQL Server have never stopped to amaze me.

Watch a quick video relevent to this subject:

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

9 thoughts on “SQL SERVER – 2012 Functions – FORMAT() and CONCAT() – An Interesting Usage

    • This one-liner may work better for you:
      select ‘Current Time is ‘ + datename(dw, getdate()) + ‘ ‘ + datename(m, getdate()) + substring(convert(char(12), getdate(), 107), 4, 9)

    • @Heri, here’s a SELECT which may work better for you:
      select ‘Current Time is ‘ + datename(dw, getdate()) + ‘ ‘ + datename(m, getdate()) + substring(convert(char(12), getdate(), 107), 4, 9)

  1. Pingback: SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video « SQL Server Journey with SQL Authority

  2. A bit nit-picky, but I believe you meant “Current Date”, not “Time” – Unless I’m missing something. Thanks for the format tip!

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #038 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s