0

I have below table with 3 columns in sql server.I want to compare [yearmonth] with a concatenation of [year] and [month] columns. like... select count(*) from table where [yearmonth]=concat([year],[month]).

Catch is here that whenever we have a [month] value of single-digit we have to add 0 as a prefix in it. like for the first record, it should become 05 instead of 5 while comparing.so final count of select count(*) from table where [yearmonth]=concat([year],[month]) should be 3

Data type of [yearmonth] is nvarchar

Data type of [year] is nvarchar

Data type of[month] is int

enter image description here

Yogee yadav
  • 49
  • 8
  • What is your actual question here? How to get `'202105'` from the `int` values `2021` and `5`? – Thom A Aug 25 '21 at 14:11
  • Does this answer your question? [Formatting Numbers by padding with leading zeros in SQL Server](https://stackoverflow.com/questions/9520661/formatting-numbers-by-padding-with-leading-zeros-in-sql-server) – Thom A Aug 25 '21 at 14:11
  • Hi @Larnu..Please check the below solution in the thread..this is i what i wanted..Thanks for looking into it. – Yogee yadav Aug 25 '21 at 14:34
  • What types are the columns? If they are `int` then you can do `year * 100 + month` – Charlieface Aug 25 '21 at 17:00

1 Answers1

1

Use the following code:

SELECT COUNT(*) FROM table 
WHERE [yearmonth]= CONCAT([year], RIGHT('00' + CAST([month] AS VARCHAR(2)), 2))
Mohammad Akbari
  • 4,486
  • 6
  • 43
  • 74