How to perform SQL concatenation

Asked

Viewed 12,287 times

4

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

5 answers

3

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.

1

  • It is not necessary to make a Cast in Ano and Mês, because the values that are passed as arguments within the function Concat are directly converted to texto

1

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

1

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

0

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.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.