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()

SQL SERVER - A Quick Trick about SQL Server 2012 CONCAT Function - PRINT concaterror

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 (https://blog.sqlauthority.com)

SQL Function, SQLAuthority Book Review
Previous Post
SQLAuthority News – Introduction to expressor Connectivity to Salesforce – expressor Data Integration Applications
Next Post
SQL SERVER – Mastering the Basics – Igniting Learning – A Unique Learning Experience

Related Posts

7 Comments. Leave new

  • Chirag Satasiya
    December 21, 2011 9:11 am

    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

    Reply
  • Thanks for this post, really nice , i learned one new thing today…Keep continue this………

    Reply
  • 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

    Reply
    • Chandrashekhar Singh
      December 30, 2011 1:01 pm

      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

      Reply
      • Yes it will be converted to varchar. But for Dates, if you want to have specific format you need to use convert function anyway

  • 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..

    Reply
    • Chandrashekhar Singh
      December 30, 2011 12:53 pm

      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

      Reply

Leave a Reply