CODEWITHSUNDEEP
sqlsql-serverconcatenation
PRIME
PRIME

Reputation: 83

Concatenate String + date in SQL Server

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

Upvotes: 5

Views: 43346

Answers (5)

LukStorms
LukStorms

Reputation: 29647

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.

Upvotes: 4

Niels Berglund
Niels Berglund

Reputation: 1698

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

Upvotes: 0

Slawomir Szor
Slawomir Szor

Reputation: 343

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

Upvotes: 1

Amirali Samiei
Amirali Samiei

Reputation: 405

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

Upvotes: -1

Keith
Keith

Reputation: 1038

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)

Upvotes: 2

Related Questions