Convert/get varchar variable to YYYYMM

I have 4 CTE’s in this table and the third one contains a DATETIME converted to VARCHAR (with format based on the requirement) as startDate in DD/MM/YYYY format. The last cte does calculations based on the data generated and one of the columns needs to store YYYYMM date based on startDate.

The problem it’s getting the year and the month from this converted DATETIME, using convert() it shows this:

IDPER
-------
01/01/ --DD/MM/

​x
 
IDPER-------01/01/ --DD/MM/​

These 2 show YYYYMM correctly when startDate isn’t converted:

Select *, left(convert(nvarchar(6),new_ini,112),6) as IDPER from table

Select *, convert(nvarchar(6),new_ini,112) as IDPER from table

 
Select *, left(convert(nvarchar(6),new_ini,112),6) as IDPER from table​Select *, convert(nvarchar(6),new_ini,112) as IDPER from table​

How could I get YYYYMM format having startDate converted? Or what could be a more smart approach to the requirement

Answer

My way would be slightly different

SELECT CONVERT(NVARCHAR(6), CONVERT(DATE, new_ini, 103), 112);

 
SELECT CONVERT(NVARCHAR(6), CONVERT(DATE, new_ini, 103), 112);​

Here, I first converted it to date and then formatted to YYYYMMDD and taken 6 chars only

Advertisement

Answer