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()
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:
- SQL Server – 2016 – New Feature: Dynamic Data Masking
- SQL Server – 2016 – T-SQL Enhancement “Drop if Exists” clause
- SQL SERVER 2016 – Comparing Execution Plans
- SQL SERVER – 2016 – Opening JSON with OPENJSON().
- SQL SERVER 2016 – New T-SQL Functions – COMPRESS – DECOMPRESS
With every release of SQL Server, Microsoft has been adding enhancements to the product. Earlier I have written below blogs talking about new features/enhancements. Now I will write about features for SQL Server 2016 which are COMPRESS – DECOMPRESS.
Reference: Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
The trick is in using this with PRINT statement more than the SELECT statement. Give it a try :) …
Nice Tips..
Thanks & Regards,
Nikhildas
Hi pinal sir,
Really useful function.
Regard$
Chirag Satasiya
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]
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..
>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
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