SQL SERVER – A Quick Trick about SQL Server CONCAT function

Just a day before I was presenting at Virtual Tech Days and I wanted to demonstrate the current time to the audience using SQL Server Management Studio, I ended up a quick error. If any of you ever tried to CONCAT function multiple values of different datatype this should not be surprising to you.

SELECT 'Current Time ' + GETDATE()

SQL SERVER - A Quick Trick about SQL Server CONCAT function concaterror

I quickly modified the script to the following workaround and my script worked right away.

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

Current Time Dec 20, 2011, 7:00PM

However, I instantly realized that I can use SQL Server 2012 function which can right fit for this situation.

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

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

Watch a quick video relevant to this subject:

Here are a few links to the blog post which you can read for additional reading:

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

, , ,
Previous Post
SQL SERVER – Video – Performance Improvement in Columnstore Index
Next Post
SQLAuthority News – Interview with Book Authors after 2 Months of Book Released

Related Posts

7 Comments. Leave new

  • The trick is in using this with PRINT statement more than the SELECT statement. Give it a try :) …

    Reply
  • Nice Tips..

    Thanks & Regards,
    Nikhildas

    Reply
  • Chirag Satasiya
    December 20, 2011 3:23 pm

    Hi pinal sir,
    Really useful function.

    Regard$
    Chirag Satasiya

    Reply
  • hi pinal,

    am new to this blog, please let me knw the book name in which all topics shld cover. topics like : backup and restore, attach and detach, export and import, log shipping, mirroring , replication, clustering and finally performance tuning … plse let me knw to my mail id [email removed]

    Reply
  • hi pinal,

    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. to my mail id [email removed]

    thankyou..

    Reply
  • >Vinod Kumar

    >The trick is in using this with PRINT statement more than the SELECT statement. Give it a try :) …

    where is the trick? did you mean to replace select with print statement?

    If yes, then I tried the same thing using print statement, but still it gives the same error.

    Following is the print statement
    PRINT ‘Current date’ + GETDATE()

    Following is the error:
    Conversion failed when converting date and/or time from character string.

    Please reply.

    Thanks,
    Joe

    Reply
  • Hi Pinal,

    I am having Denali CTP 3. When i am trying concat function or new introduced functions, this will give me error that function not found. Can you give me
    version information on which you test concat function.

    My version information is below

    Microsoft SQL Server Management Studio 11.0.1440.19

    Thanks
    Vinay Kumar

    Reply

Leave a Reply

Menu