0

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.

apomene
  • 14,282
  • 9
  • 46
  • 72
domdew
  • 61
  • 1
  • 2
  • 9

3 Answers3

4

You can also do it like this:

select dateadd(year,-1,dateadd(dd,-1,getdate()))
SqlKindaGuy
  • 3,501
  • 2
  • 12
  • 29
3

If you want the date, then convert to date:

select convert(date, dateadd(year, -1, getdate() - 1))
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
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
apomene
  • 14,282
  • 9
  • 46
  • 72
  • 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