Concatenate 3 text columns

Question

Post reply

Concatenate 3 text columns

pedroccamara

SSC-Addicted

Points: 431
More actions
October 9, 2022 at 7:19 pm
Go to Answer
#4097210

Hello all
I know it's Sunday and we're all supposed to be resting for the week to come. But this issue is intriguing me.
In my SQL code, after the selected fields I've made two CASE WHEN. After, I'd like to concatenate those two and another column, Year so I can have my date field. Let me show you the code:
,CASE M.Dia
WHEN '0' THEN '1'
ELSE M.Dia
END
AS DD
,CASE M.Mes
WHEN '0' THEN '1'
WHEN '13' THEN '12'
ELSE M.Mes
END
AS MM
So far so good. I run it and it works as expected.
Now, I want to concatenate DD, MM and M.Year so I can have the date.
Somehow it doesn't recognize DD nor MM.
Can any of you help me on how to concatenate those 3 fields?
Thank you all so much
And I'm sorry to boder you on a Sunday
Thank you all in advance
Pedro

frederico_fonseca

SSCoach

Points: 16154

October 9, 2022 at 9:57 pm

Answer

#4097241

you were clear - but you did NOT show us the code where you are trying to concatenate the fields - without that we can't tell you what you did wrong.

and please use the code block to put your code in - either on the edit button or by enclosing it in [ code] [ /code] tags (without the space)

Code formatted below with a way to do it - not necessarily the best one

SELECT M.ContaOrigem
     , M.Conta
     , M.Serie
     , M.Utilizador
     , M.Documento
     , M.NumDoc
     , M.Diario
     , M.NumDiario
     , M.DataGravacao
     , M.Natureza
     , M.Descricao
     , M.Valor
     , M.TipoConta
     , M.Ano
     , datefromparts(M.Ano, convert(tinyint, dates.MM), convert(tinyint, dates.DD)) as newdate
FROM Movimentos M
outer apply (select CASE M.Dia
                      WHEN '0' THEN '1'
                      ELSE M.Dia
                      END
                    AS DD
                  , CASE M.Mes
                      WHEN '0' THEN '1'
                      WHEN '13' THEN '12'
                      ELSE M.Mes
                    END
                    AS MM
            ) dates

WHERE M.Ano >= 2019
  AND M.Diario <> '72'
  AND M.TipoConta = 'F'

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

you didn't really post your code here neither the error you got - but look at function datefromparts which will allow you to do that easily.

pedroccamara SSC-Addicted Points: 431 More actions · Answer 2

Thanks Frederico for your answer

I don' tknow why but I thought it was clear. No problem. I'll just add all the code

SELECT M.ContaOrigem

,M.Conta

,M.Serie

,M.Utilizador

,M.Documento

,M.NumDoc

,M.Diario

,M.NumDiario

,M.DataGravacao

,M.Natureza

,M.Descricao

,M.Valor

,M.TipoConta

,M.Ano

,CASE M.Dia

WHEN '0' THEN '1'

ELSE M.Dia

END

AS DD

,CASE M.Mes

WHEN '0' THEN '1'

WHEN '13' THEN '12'

ELSE M.Mes

END

AS MM

FROM Movimentos M

WHERE M.Ano >= 2019 AND M.Diario <>'72' AND M.TipoConta = 'F'

The purpose is to concatenate DD, MM and M.Ano like this for example 01-01-2022. They're all text columns

Thanks a lot Frederico

pedroccamara SSC-Addicted Points: 431 More actions · Answer 3

Hi Frederico,

Thank you so much for your answer. I got a couple of questions before my code, if this ok with you.

The first and the second CASE are DD and MM. Why dates.DD and dates.MM on datefromparts?

Why datefromparts and not just concatenate?

Finally, why outer apply and select and not just CASE. like it is in my code?

Here's my code

SELECT M.ContaOrigem

,M.Conta

,M.Serie

,M.Utilizador

,M.Documento

,M.NumDoc

,M.Diario

,M.NumDiario

,M.DataGravacao

,M.Natureza

,M.Descricao

,M.Valor

,M.TipoConta

,M.Ano

,M.Mes

,M.Dia

,CONCAT(DD, '-', MM, '-', M.Ano,'')

,CASE M.Dia

WHEN '0' THEN '1'

ELSE M.Dia

END

AS DD

,CASE M.Mes

WHEN '0' THEN '1'

WHEN '13' THEN '12'

ELSE M.Mes

END

AS MM

FROM Movimentos M

WHERE M.Ano >= 2019 AND M.Diario <>'72' AND M.TipoConta = 'F'

Thanks a lot

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

pedroccamara wrote:

Hi Frederico,
Thank you so much for your answer. I got a couple of questions before my code, if this ok with you. The first and the second CASE are DD and MM. Why dates.DD and dates.MM on datefromparts? Why datefromparts and not just concatenate? Finally, why outer apply and select and not just CASE. like it is in my code?

Frederico asked you to use a code block. I assume that you were unable to understand what that means. In the formatting toolbar, there is a button containing the word 'Code'. Click that and then paste your code into the popup window which appears, then click OK. The code block will be added to your thread.

DateFromParts gives you a date, not a string which looks like a date (and may be invalid, which DateFromParts would catch automatically).

I suspect that the OUTER APPLY was used to make the SELECT part of the query neat and tidy. As everything in the APPLY comes from the main table, I'd have used CROSS APPLY here, but the outcome would be the same.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

using cross/outer apply means that you can then use the new columns elsewhere as I did - making the code cleaner.

so on your own example with your concat ",CONCAT(DD, '-', MM, '-', M.Ano,'')" had you used the cross/outer apply you could have replaced used the alias - that was your error e.g. trying to use an alias created on the same level.

with regards to using datefromparts you said "Now, I want to concatenate DD, MM and M.Year so I can have the date." - concatenate does not give you a date, while datefromparts does - if all you wanted was a string representing a date in a specific format you should have made that clear.

pedroccamara SSC-Addicted Points: 431 More actions · Answer 6

Hi Phil

Thanks a lot for stepping in.

I believe I don't have that button... should I enable something, somewhere?

apagar

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

That looks like the SSMS toolbar. I am talking about the toolbar which is displayed when you create a post here:

CodeBlock

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 8

even if you don't have the buttons you can use the tags as I mentioned.

pedroccamara SSC-Addicted Points: 431 More actions · Answer 9

Hi Frederico,

I'm so sorry.

Again I thought it was obvious (my mistake) that I wanted a new date field from that concatenate.

Could you please help me then? How would you do it? Because it still doesn't recognize the MM nor the DD

,datefromparts(M.Ano, convert(tinyint,MM), convert(tinyint,DD)) as newdate

apagar2

pedroccamara SSC-Addicted Points: 431 More actions · Answer 10

It's working now !!!! I forgot the " ) dates" after END AS MM

Thank you guys so much

Thank you Frederico and Phil

Best regards

Have a great day