Query to add Year to DateTime in SQL Server

Last Reply 4 months ago By dharmendr

Posted 4 months ago

Hi! I used below code, but could'n get required result:

declare @num int, @date date, @res int
set @date = '14.04.2017'
set @res = CONVERT(VARCHAR,CONVERT(INT,DATEDIFF(DD,@date,GETDATE())/365.25))
set @num = 0
while(@num <= @res)
	begin
		if(@num = 0)
			print @date
		else if(@num > 0)
			print DATEADD(year, 1, @date)
		set @num = @num + 1
	end

Result:

2017-04-14

2018-04-14

2018-04-14

I need below result:

2017-04-14

2018-04-14

2019-04-14

You are viewing reply posted by: dharmendr 4 months ago.
Posted 4 months ago

Hi PRA,

PRA says:
print DATEADD(year, 1, @date)

You need to pass @num instead of 1.

Refer below sample query.

SQL

declare @num int, @date date, @res int
set @date = '2017-04-14'
set @res = CONVERT(VARCHAR,CONVERT(INT,DATEDIFF(DD,@date,GETDATE())/365.25))
set @num = 0
while(@num <= @res)
begin
    if(@num = 0)
	BEGIN
        print @date
	END
    else if(@num > 0)
	BEGIN
        print DATEADD(year, @num, @date)
	END
    set @num = @num + 1
end

Output

2017-04-14

2018-04-14

2019-04-14