SQL SERVER – A Quick Trick about SQL Server 2012 CONCAT Function – PRINT

Yesterday I posted A Quick Trick about SQL Server 2012 CONCAT function and the very first comment in few minutes of Vinod Kumar. He suggested that this function should be also used with the PRINT statement as well. While I was having conversation with him – Jacob Sebastian sent me message suggesting the same. As I got feedback in first 10 minutes of publishing the blog post – I decided to update the blog post. While I started to write there was an email from Rick Morelan suggesting that this function can be used along with PRINT statement. Alright – 3 SQL experts suggesting the same thing to me in less than 15 minutes. Here is the dedicated post addressing the same idea.

Here is the question how many of you had the same suggestion as Vinod, Jacob and Rick?

Here is the updated version of the same article. If you use PRINT statement and directly appending string and datetime datatype you will get following error.

PRINT 'Current Time ' + GETDATE()

The normal reaction to the same is to modify the script and convert datetime datatype to varchar datatype. This will not only remove the error but also a very common practice.

PRINT 'Current Time ' + CAST(GETDATE() AS VARCHAR(20))

Current Time Dec 21 2011  7:00PM

Just like earlier blog post you can now use SQL Server 2012 function CONCAT to resolve the error.

PRINT CONCAT('Current Time ', GETDATE())

I have previously written in detail article about SQL Server 2012 CONCAT function over here additionally, I have written quick guide on 14 new functions of SQL Server Denali.

I am sure Vinod, Jacob and Rick are smiling at looking at this blog post.

Watch a quick video relevent to this subject:

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

About these ads

9 thoughts on “SQL SERVER – A Quick Trick about SQL Server 2012 CONCAT Function – PRINT

  1. Hi pinal sir,

    Thanks to Vinod, Jacob and Rick for this suggestion.

    There are lots of Good function coming with SQL Server 2012.
    Thank you for updating.

    Regard$
    Chirag Satasiya

  2. Hai Pinal Sir,

    The Concatenation Function will also work in older version of SQL Server With as CANONICAL Function.

    SELECT {fn CONCAT(‘Current Time :’, Cast(GETDATE() as varchar(20)))}

    PRINT {fn CONCAT(‘Current Time :’, Cast(GETDATE() as varchar(20)))}

    But it won’t work without casting the getdate()

    Please let me know ,If you feel any wrong from me.

    Thanks & Regards,
    Nikhildas

    • Hi Nikhil,
      It is not required to use the Concat() function only. Pinal is trying to say that without any data coversion we can concatenate.
      Try this for your clarification-
      PRINT ‘Current Time :’+ Cast(GETDATE() as varchar(20))
      With the CONCAT()(SQL 2012 feature) function you need not to do any explicit Data Coversion.
      Pinal, let me know if I am wrong.

      Regards,
      Chandrashekhar

  3. hi pinal sir,

    am having a query, please tel the steps for this query..

    The database is used by a group of individuals. It appears to now be locked. Is there any way to tell who is the owner of XYZ ?

    And say me steps to unlock the database.

    thankyou..

    • Hi Kiran,
      Please try the below query to check the Owner- select suser_sname(owner_sid) from sys.databases where name = ‘DBNAME’

      Do you want to Unlock a user or a DB?
      Regards,
      Chandrashekhar

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

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

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