T-SQL (sql server) question

Evil Phil

Folks,

I need to calculate a date, that date being a given date (getdate() below) + 1 year - 1 day. So that is:

date + (1 year - 1 day).

With me so far? okay. Now I have code that does this for me but is there a better way of doing it?

declare @certYear int

if (YEAR(getdate()) % 4) > 0 /* take into account leap years */
	begin
		select @certYear = 364
	end
else
	begin
		select @certYear = 365
	end

select getdate() + @certYear

bonkey

Use the DATEADD function - once to add a year, once to subtract a day.

Use it nestedly if you want to do it all on one line.

e.g.

select DATEADD(DATEADD(getdate(), 1, yy), -1, dd)

(Or something like that....check books online for the correct syntax)

jc

Evil Phil

Thanks Bonkey, if anybody is wondering:

select DateAdd(day, -1, DATEADD(year, 1, getdate())) 

bonkey

Goshdurnit....had the parameters the wrong way round.

/me hangs head in shame.

(You're welcome though)

amen

just as an aside a normal year has 365 days and a leap year 366!

Evil Phil

Aye, but its a year minus one day hence 364 (for normal years) and 365 (for leap years).