Update a column using two variables

user_14194140 0
Viewed 31

My code is

DECLARE @nextYear Integer = 0;
select @nextYear = DATEPART(yy,getdate())+1

DECLARE @nextMonth DATETIME = GETDATE();
DECLARE @mon integer = 0;
SELECT @mon = MONTH(EOMONTH ( @nextMonth, 1 )) 

update Mytable set
month_of_usage = '@mon/@nextYear' where id = 1

Data type of month_of_usage is varchar. I need month of usage to be set as 11/2021. This script is meant to automatically populate data for next month.

sql-server
2 Answers

'@mon/@nextYear' would mean the literal string value '@mon/@nextYear'; not the value of @mon followed by / followed by the value of @nextYear.

You need to concatenate the values:

UPDATE MyTable
SET month_of_usage = CONCAT(RIGHT(CONCAT('00',@mon),2),'/',@nextYear)
WHERE id = 1;

I also use RIGHT to create a value that always have the same format, MM/yyyy, as otherwise you would get values like '1/2020' and '10/2020'.

Of course, that looks like it's attempting to represent a date, and a varchar is the completely wrong data type for that. For example, according to your data, 01/2020 is before 12/1999. If you need to store date related data, use a Date and Time Data type; there are 6 to choose from and at least one of them will be appropriate.

user_20299830

You can try this

update Mytable set
month_of_usage = Cast(@mon as varchar(10)) + '/' + Cast(@nextYear as Varchar(10)) where id = 1
user_105325000
Related