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)
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
your_table_name with the actual name of your table.
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.
CAST(... AS VARCHAR(10)) converts the date to a string in the format 'YYYY-MM-DD'.
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.