Geek Answers Handbook

SQL Subtract exactly one year

First, I want to thank everyone for helping me, it gave me a lot of ideas on how I should do this, and I came up with my own method. I just need help inserting it into the request

I want the user to enter a date and then get the current year. subtract two, then make dateadd, which everyone posted. Is this possible, and what is the best way to do this?

year(getdate()) -@DYYYY =Y  dateadd(year,-Y,getdate())
+6
source share
6 answers

Based on your comment regarding hardcoded year values, use

  DATEDIFF(year,BOOKED,GETDATE())

in order to get the number of years from the moment when after that you must lead you in the direction you are in.

You will probably get something like:

 SELECT DATEADD(year, -DATEDIFF(year,BOOKED,GETDATE()), GETDATE())

Well, this looks more like everything you really want to do (maybe I'm wrong, sorry if that is the case) is to group orders by year.

Will the result be the following help?

 SELECT SDESCR,DATEADD(YEAR, DATEDIFF(YEAR, 0, BOOKED),0), Sum(APRICE) as Total, Sum(PARTY) as PAX FROM DataWarehouse.dbo.B01Bookings AS B101Bookings GROUP BY SDESCR,DATEADD(YEAR, DATEDIFF(YEAR, 0, BOOKED),0)

As I said, this is a hunch about your goal, not necessarily the answer to your question.

+8
source

Use this:

 dateadd(year, -1, getdate())
+33
source

OK ... From your answer to Joe Stefanelli, I think part of what you are really trying to do is not to rewrite the request every year. If so, then you can create a table of numbers. A simple and quick example would be like this ...

  SELECT TOP 3000 IDENTITY(INT,1,1) as N INTO #Numbers FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2

Instant table with integers from 1 to 3000.

This allows you to join or query the #Numbers table for your query. If you want to make a more limited range, then you can create a table in just a few years (or a function with a table estimate that will do the same table dynamically).

You could also take it a little further and implement a table-valued function that will return a two-column result

 year offset 2010 0 --Produced from DATEPART(y,GETDATE()) 2009 -1 --loop/set subtract 1 2008 -2 --repeat until you have enough...

So your where clause could read something like

 SELECT * FROM yourTable, yourFunction WHERE ((DYYYY = CAST(yourFunction.year as VARCHAR) AND (BOOKED <= DATEADD(yy, yourFunction.offset, GETDATE()))

Please note that while tv functions should save you some time programming maintenance, you may run into small performance losses.

+3
source

To subtract the year from the date, simply use the DATEADD () function

 SELECT DATEADD(year, -1, GETDATE())

Edited by:

 SELECT SDESCR,DYYYY, Sum(APRICE) as Total, Sum(PARTY) as PAX FROM DataWarehouse.dbo.B01Bookings AS B101Bookings WHERE (BOOKED <= Convert(int, Convert(datetime, Convert(varchar, DatePart(month, GETDATE())) + '/' + Convert(varchar, DatePart(day, GetDate())) + '/' + DYYYY)) Group By SDESCR,DYYYY Order by DYYYY

Edited 2:

I just ran this statement

 select Convert(datetime, Convert(varchar, DatePart(month, GETDATE())) + '/' + Convert(varchar, DatePart(day, GetDate())) + '/' + '2007')

Which is working fine. so my question is what is stored in the DYYYY column? Does it always contain a year, can it contain Nulls?

+2
source 
 select DATEADD(yy, -1, getdate())
0
source

The easiest way to get the date 1 year ago:

 SELECT GETDATE() - 365
0
source

More articles:


All Articles