I have the following data:
KEY ID DATE
123456789 09BA2038 01-01-2017
And I would like to concatenate it, but keep the original format of the date. When I try:
CONCAT(Key, '-', ID, '-', DATE)
it gives me an output of
123456789-09BA2038-Jan 01 2017 11:00AM
But I would like the output to be
123456789-09BA2038-01-01-2017
If you're using SQL Server 2012 or newer, then you can use FORMAT to change a date or datetime into a varchar with the format of your liking.
select CONCAT([Key],'-',ID,'-',FORMAT([DATE],'MM-dd-yyyy')) as Key2
from (values (123456789,'09BA2038',convert(date,'2017-01-15',126))) v([Key],ID,[DATE]);
Result:
Key2
123456789-09BA2038-01-15-2017
Or you could use CONVERT instead using the 110 style for the USA date format.
Convert the date, I am guessing you want the format mm-dd-yyyy, if so:
CONCAT([key],'-',[ID],'-',CONVERT(VARCHAR(10), [DATE], 110))
If you want dd-mm-yyyy it is:
CONVERT(VARCHAR(10), [DATE], 105)
You need to use an explicit convert to get the date format you want.
In this case CONCAT(Key, '-', ID, '-', convert(varchar(10),DATE,105)) should work fine.
You can find the full list of formats here: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
Try CONCAT(Key, '-', ID, '-', CONVERT(varchar(10),DATE, 110)). The 110 tells SQL Server to format the date as 'mm-dd-yyyy' (US style).
For more information about this look here.
Niels
I think not using concat is better in this situation like this:
select convert(nvarchar(100),KEY)+'-'+convert(nvarchar(100),ID)+'-'+convert(nvarchar(100),DATE)
from tableName
+? A CONCAT seems better when some of the values can allow NULL's and you still want to return something. F.e. select concat('x','-y'+null) as v2
concat is any different from + in this sense, and neither should rely on concatenating a null yielding anything other than null because the alternative is (horrible, non-ANSI, and) deprecated: learn.microsoft.com/en-us/sql/t-sql/statements/…