How to perform SQL concatenation

Question

I need to concatenate year and month. How could I do that?

    select cast(year(orderdate) as int) + ' ' + cast(month(OrderDate)as int) as Year_Month
    From sales.SalesOrderHeader

Answer 1

Uses the function CONCAT.

SELECT CONCAT(YEAR(OrderDate), '-', MONTH(OrderDate)) AS Year_Month
FROM sales.SalesOrderHeader

In this case, the second parameter of the Concat is the separator ('-') that you define what will be.

Answer 2

You can use sql CONCAT

https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql

select Concat(cast(year(orderdate) as int),  '_'  ,cast(month(OrderDate)as int)) as Year_Month From sales.SalesOrderHeader

Answer 3

They gave the option of CONCAT, but he was imploded in SQL SERVER 2012, is very likely to be using the 2008 and is incompatible.

To make the concatenation, it must be a string. The way you are doing error will occur, so consider it as follows using YEAR() and MONTH with CAST():

SELECT CAST(YEAR(orderdate) AS NVARCHAR(4)) + ' ' + CAST(MONTH(orderdate) AS NVARCHAR(2)) AS year_month

Or using DATEPART:

SELECT CAST(DATEPART(yyyy, orderdate) AS NVARCHAR(4)) + ' ' + CAST(DATEPART(mm, orderdate) AS NVARCHAR(2)) AS year_month

Answer 4

CONCAT (Transact-SQL)

Returns a string that is the result of concatenating two or more string values. SQL-Server 2012 partition is available.

Query:

select CONCAT('O ano em que estamos é ', YEAR(GETDATE()),' e o mês é ', 
MONTH(GETDATE())) as Data

Note: All arguments are implicitly converted into string types and then concatenated. So it is not necessary to use T-SQL CAST as you ask.

Sqlfiddle

Answer 5

Joana, in the sql code you posted, keep an eye out for the months whose numerical value is less than 10. For example, 12/11/2008 will be "2008 11" but 12/1/2008 will be "2008 1", when it seems to me that the expected is "2008 01".

A simple and quick way to get what you want is to use the CONVERT() function, converting from date for string. Something like that:

-- código #1
SELECT convert (char(7), orderdate, 23) as Year_Month
  from sales.SalesOrderHeader;

If you don’t want the hyphen between year and month, here’s a variation:

-- código #2
SELECT replace (convert (char(7), orderdate, 23), '-', ' ') as Year_Month
  from sales.SalesOrderHeader;

Note that with this solution it is not necessary to use an operator or concatenation function nor to worry about months whose value is less than 10. But if you are interested in concatenating, I suggest reading the article Concatenation of strings.