Convert string to date and display Month name and Year

Question

Hi, I'm looking for help. Here is my code. I want output when i pass...depends on Month and Year, values should display. id=1 October, 2023 id=2 November, 2023

create table dbo.table1(yearmonth varchar(20), id int)
insert into table1 (yearmonth, id)
select '2023-10',1
union
select '2023-11',2
declare @val varchar(20)
set @val =  (select yearmonth from table1 where id=1)
-- Convert the string to a date
DECLARE @dv DATE = CONVERT(DATE, @val);
-- Display the formatted date
SELECT FORMAT(@dv , 'MMMM, yyyy') AS FormattedDate from Table1

I'm getting an error - Conversion failed when converting date and/or time from character string.

Answer 1

Check an example:

select id, format(cast(yearmonth + '-01' as date), 'MMMM, yyyy') 
from dbo.table1

Answer 2

Hi Dinesh Kalva i think its because the yearmonth column in your table1 table is of type VARCHAR, and you're trying to directly convert it to a DATE data type. you should first convert the yearmonth string to a DATE or DATETIME let me give you a updated code

CREATE TABLE dbo.table1 (yearmonth VARCHAR(20), id INT);

INSERT INTO table1 (yearmonth, id)
VALUES
    ('2023-10', 1),
    ('2023-11', 2);

DECLARE @val VARCHAR(20);
SET @val = (SELECT yearmonth FROM table1 WHERE id = 1);

-- Convert the string to a date
DECLARE @dv DATE = CONVERT(DATE, @val + '-01'); -- Concatenate '-01' to make it a complete date
-- Display the formatted date
SELECT FORMAT(@dv, 'MMMM, yyyy') AS FormattedDate FROM Table1;

If this helps kindly accept the answer thanks much.