Concatenate Year and Month

Question

Hi,

I have 2 date fields, surv_year (int) and surv_month (text).

Example outputs:
surv_year: 2022
surv_month: January

surv_year: 2022
surv_month: August

Is there anyway to concatenate the 2 and change the month from a text to a number and have my final output as YYYYMM with my example above showing 202201 and 202208?

Answer 1

Select  Format(try_cast(concat_ws(' ',surv_year,surv_month,'1') as date),'yyyyMM') [yyyyMM]  
 from yourtable   

Answer 2

Hi @Bone_12 ,

Please try the following solution.
It will work starting from SQL Server 2017 onwards due to dependency on the CONCAT_WS() function.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, surv_year int, surv_month VARCHAR(20));  
INSERT @tbl (surv_year, surv_month) VALUES  
(2022, 'January'),  
(2022, 'August');  
-- DDL and sample data population, end  
  
SELECT t.*  
	, realDate  
	, result = FORMAT(realDate, 'yyyyMM')  
FROM @tbl AS t  
	CROSS APPLY (SELECT TRY_CAST(CONCAT_WS('-', surv_year, surv_month, '01') AS DATE)) AS t1(realDate);  

Output

+----+-----------+------------+------------+--------+  
| ID | surv_year | surv_month |  realDate  | result |  
+----+-----------+------------+------------+--------+  
|  1 |      2022 | January    | 2022-01-01 | 202201 |  
|  2 |      2022 | August     | 2022-08-01 | 202208 |  
+----+-----------+------------+------------+--------+  

Answer 3

Create a mapping table "month name" => "month number" for your query.

Answer 4

Hi @Bone_12 ,

Try this:

create  TABLE tb (ID INT IDENTITY PRIMARY KEY, surv_year int, surv_month VARCHAR(20));  
 INSERT into tb (surv_year, surv_month) VALUES  
 (2022, 'January'),  
 (2022, 'August'),  
 (2022,'December');  
  
select *,CONCAT([surv_year] , month(surv_month+'1,1')) as [date]  from tb  

Best regards,
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

You can do it with a concat:

SELECT YEAR(getdate()) || MONTH(getdate()) AS 'yyyymm'

OR

SELECT YEAR(getdate()) || '-' || MONTH(getdate()) AS 'yyyy-mm'

Replace the getdate() with your own date field.

Mark the answer if it worked! ;-)