SQL Subtract exactly a year
Asked Answered
C

7

27

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 putting it into a query

I want the user to input a date, then get the current year. subtract the two and then do the dateadd that everyone was posting. Is this possible and what is the best way to do it?

year(getdate())-@DYYYY=Y 
dateadd(year,-Y,getdate())
Confront answered 22/11, 2010 at 15:33 Comment(3)
What dialect of SQL? MSSQL? Oracle? MySQL? ... Date functions and operations are not exactly the same across different databases.Grum
What SQL server are you using? (MS SQL, MySQL, Oracle, etc)Atrabilious
Instead of the user selecting the DYYYY, can you get them to select an offset number of years? e.g. instead of selecting '2011' they select offset zero, and instead of selecting '2010' they select offset of -1, etc? Then you should be able to write your code so it will work for any date in any year.Eldwon
E
13

Based on your comment regarding hard coded year values, the use of

 DATEDIFF(year,BOOKED,GETDATE())

to get the number of years since the date you're after should hopefully lead you in the direction you are after.

You will probably end up with something like:

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

Ok, it looks more like all you really want to do (I may be wrong, sorry if so) is to group the bookings by year.

Will the result of the following help achieve that?

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 guess as to your goal, not necessarily an answer to your question.

Eyecatching answered 22/11, 2010 at 15:55 Comment(2)
but i dont get how that would go into my where statement SELECT SDESCR,DYYYY, Sum(APRICE) as Total, Sum(PARTY) as PAX FROM DataWarehouse.dbo.B01Bookings AS B101Bookings WHERE (DYYYY = '2007') AND (BOOKED <= CONVERT(int, GETDATE() - 1461)) OR (DYYYY = '2008') AND (BOOKED <= CONVERT(int, GETDATE() - 1096)) OR (DYYYY = '2009') AND (BOOKED <= CONVERT(int, GETDATE() - 731)) OR (DYYYY = '2010') AND (BOOKED <= CONVERT(int, GETDATE() - 365)) OR (DYYYY >= '2011') AND (BOOKED <= CONVERT(int, GETDATE())) Group By SDESCR,DYYYY Order by DYYYYConfront
ill put it up top so you can see it betterConfront
B
88

Use this:

dateadd(year, -1, getdate())
Bangup answered 22/11, 2010 at 15:35 Comment(0)
E
13

Based on your comment regarding hard coded year values, the use of

 DATEDIFF(year,BOOKED,GETDATE())

to get the number of years since the date you're after should hopefully lead you in the direction you are after.

You will probably end up with something like:

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

Ok, it looks more like all you really want to do (I may be wrong, sorry if so) is to group the bookings by year.

Will the result of the following help achieve that?

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 guess as to your goal, not necessarily an answer to your question.

Eyecatching answered 22/11, 2010 at 15:55 Comment(2)
but i dont get how that would go into my where statement SELECT SDESCR,DYYYY, Sum(APRICE) as Total, Sum(PARTY) as PAX FROM DataWarehouse.dbo.B01Bookings AS B101Bookings WHERE (DYYYY = '2007') AND (BOOKED <= CONVERT(int, GETDATE() - 1461)) OR (DYYYY = '2008') AND (BOOKED <= CONVERT(int, GETDATE() - 1096)) OR (DYYYY = '2009') AND (BOOKED <= CONVERT(int, GETDATE() - 731)) OR (DYYYY = '2010') AND (BOOKED <= CONVERT(int, GETDATE() - 365)) OR (DYYYY >= '2011') AND (BOOKED <= CONVERT(int, GETDATE())) Group By SDESCR,DYYYY Order by DYYYYConfront
ill put it up top so you can see it betterConfront
T
8

To subtract a year from a date simply use DATEADD() function

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

Edited:

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 runs fine. so my question is what is stored in the DYYYY Column? Does it always contain a valid year, can it contain Nulls?

Tedder answered 22/11, 2010 at 15:36 Comment(13)
The problem is what about next year, when the year changes. My 2009 selection will always be coded for DATEADD(yy, -1, getdate()) , but it will have to be DATEADD(yy, -2, getdate()) and the year after it should be DATEADD(yy, -3, getdate()). how will i change itConfront
@newprogrammer... Not sure but perhaps my edited answer will give you some help.Tedder
i got an error conversion failed when converting the varchar value '/' to data type int.Confront
@newProgrammer yep sorry about that..give me a minute I will fix.Tedder
am i supposed to add another convert to fix that?Confront
thank you for helping me, but now i get a error converting datetime from char string. i will post the exact query i tryed up topConfront
@NewProgrammer ... please read the comment my edit and get back to me.Tedder
The booking date is always a year however the problem may lie in the fact my booked is stored as a 37995 or as a floatConfront
@NewProgrammer... yes that could cause a problem. If you show some sample data I could help but you could simply convert again.Tedder
In my other program i used CONVERT(INT, DATEADD(dd, DATEDIFF(dd, 0," & "'" & rundate & "'" & " ), 0))+2Confront
where rundate would be a regular date and that is how it converted the date to the format of the booked dataConfront
@NewProgrammer ... try Convert(int, Convert(datetime, Convert(varchar, DatePart(month, GETDATE())) + '/' + Convert(varchar, DatePart(day, GetDate())) + '/' + DYYYY)) Note I edited the query above too.Tedder
could i just do this on runtime instead in an easier wayConfront
C
3

OK... From your response to Joe Stefanelli, I think that part of what you are really trying to do is avoid having to rewrite the query each year. If that is the case, then you can create a numbers table. A simple and fast 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 subselect against the #Numbers table for your query. If you want to make a more limited range, then you can make a table with just the years you like (or a table valued function that will make the same table dynamically).

You could also take it a bit 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...

This way, 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()))

Note that while the tv functions should save you a bit of maintenance programming each year, you may suffer some minor performance hits.

Charcot answered 22/11, 2010 at 19:25 Comment(3)
uh... in the first example, you can prepopulate the table with whatever years you like. In the second one, you are dynamically generating the list from the current year backwards.Charcot
thank you for your help, i came up with my own method i just need help implementing it year(getdate())-@DYYYY=Y dateadd(year,-Y,getdate())Confront
I actually ran into problems in my method and realized creating a table like your would be best, how could i go about creating a table that will automatically adjust the offset, for such as (2010, 0) (2009, -1) and then next year (2011,0), (2010,-1), (2009,-2)Confront
P
2

The simplest way to get the date 1 year ago is:

SELECT GETDATE() - 365
Pragmatism answered 10/6, 2019 at 21:56 Comment(1)
May cause an issue if its a "leap" year: en.wikipedia.org/wiki/Leap_yearShamus
V
1
select DATEADD(yy, -1, getdate())
Vision answered 22/11, 2010 at 15:36 Comment(1)
The problem is what about next year, when the year changes. My 2009 selection will always be coded for DATEADD(yy, -1, getdate()) , but it will have to be DATEADD(yy, -2, getdate()) and the year after it should be DATEADD(yy, -3, getdate()). how will i change itConfront
B
-1

MOST SIMPLE WAY OF GETTING EMPLOYEES WORKING IN AN ORGANISATION BEFORE 1 YEAR, WE CAN ASSUME TO GIVE THEM APPRAISAL & SLECTING ONLY THOSE WHO HAVE FINISHED 1 YEAR IN AN ORGANISATION.

BASICALLY FROM TODAY'S DATE I AM SUBSTRACTING 1 YEAR.

SELECT * FROM employees
      WHERE date_of_joining < (SELECT DATE_SUB(CURDATE(),INTERVAL 1 YEAR))
;

Berhley answered 30/3 at 10:19 Comment(1)
stop screaming please (edit)Shetler

© 2022 - 2024 — McMap. All rights reserved.