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:
[youtube=http://www.youtube.com/watch?v=HbbRpg-tHz4]
Reference: Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
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
Thanks for this post, really nice , i learned one new thing today…Keep continue this………
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
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..
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