Need to find out the date 1 year and 1 year ago from today in SQL

Question

I need to find out the date 1 year and 1 day ago from today in T-SQL. I have done the following but I keep getting errors.

select DATEADD(YEAR, - 1, CAST(CAST(GETDATE() AS DATE) AS DATETIME)), 
DATEADD(DAY, - 1, CAST(CAST(GETDATE() AS DATE) AS DATETIME)))

Can someone please assist. Thanks.

add the sql-server tag. Alsom you don't need to cast getdate to date and then to datetime; it is already a datetime — George Menoutis, Jun 29 '18 at 11:43

score 4 · Accepted Answer · answered Jun 29 '18 at 11:45

4

You can also do it like this:

select dateadd(year,-1,dateadd(dd,-1,getdate()))

answered Jun 29 '18 at 11:45

SqlKindaGuy

apomene · Answer 2 · 2018-06-29T11:50:29.863

2

Try like this:

SELECT DATEADD(DAY, -366, GETDATE())

EDIT:

Updated code as per comments, (used this answer )

CASE WHEN ISDATE(CAST(@YEAR AS char(4)) + '0229') = 1 
THEN
  SELECT DATEADD(DAY, -367, GETDATE())
ELSE 
   SELECT DATEADD(DAY, -366, GETDATE())
END

edited Jun 29 '18 at 11:50

answered Jun 29 '18 at 11:44

apomene

2

And what about leap years? – sticky bit Jun 29 '18 at 11:46
You will have a propblem on leap years since they have 366 days. – SqlKindaGuy Jun 29 '18 at 11:47
This is wrong, what about needing this when we are no longer in leap year? You can't expect to remember to change this on time before a break every few years. And this doesn't check on the full leap year, it only checks on leap year day, meaning this will result incorrect every other day. – LucasM Jan 29 '20 at 16:49

score 2 · Answer 3 · answered Jun 29 '18 at 11:49

2

If you want the date, then convert to date:

select convert(date, dateadd(year, -1, getdate() - 1))

answered Jun 29 '18 at 11:49

Gordon Linoff

3 Answers3