Need help concatenating two fields and making it first of month

select distinct cast(CONVERT(VARCHAR(10), [year], 105) + '-' + convert(varchar(10), [month], 105)) as FirstDay

The columns are year and month and I want to concatenate and then make them the first day of the month so it would be 2021-01-01 2021-02-01 2021-03-01 etc.........

SELECT DATEFROMPARTS([year], [month], 1)
  FROM yourTable

Instead of trying to concatenate strings - just use the values to generate an actual date.

To convert the "year" and "month" columns into the first day of the month in the format 'YYYY-MM-DD', you can use the DATEFROMPARTS function in SQL Server. Here's the modified query:

    CAST(DATEFROMPARTS([year], [month], 1) AS VARCHAR(10)) AS FirstDay

Replace your_table_name with the actual name of your table.


  1. DATEFROMPARTS([year], [month], 1) takes the "year" and "month" columns and combines them with day 1, creating the first day of the respective month as a date.
  2. CAST(... AS VARCHAR(10)) converts the date to a string in the format 'YYYY-MM-DD'.
  3. The DISTINCT keyword is used to ensure that only unique first day values are returned.

With this query, you will get the desired output of the first day of each month in the format 'YYYY-MM-DD', such as '2021-01-01', '2021-02-01', '2021-03-01', and so on.