SQL Subtract Exactly a Year

SQL Subtract exactly a year

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.

Subtract year and month

You were almost there, I think you need:

SELECT LEFT(CONVERT(varchar(6),DATEADD(YEAR, -1, DATEADD(MONTH,-1, GETDATE())),112),6)

When you define a varchar (in a convert, create or any other way) you should always define the length as well.

PostgreSQL Subtract exactly a year from a date field PSQL

This works in PostgreSQL by simply using INTERVAL and passing in a string of increment values and type (seconds, minutes, hours, days, months, years) etc.

UPDATE patients SET birthday = birthday + INTERVAL '1900 years';

Found idea here https://www.postgresql.org/docs/9.1/static/functions-datetime.html

Datetime Subtraction without taking count of currentDateTime and year in sql

You can try this:

update table 
set EndDateTime=case when PreviousDateTime is null then DATEADD(y,-1,EndDateTime) else PreviousDateTime end,
PreviousDateTime=case when PreviousDateTime is null then DATEADD(y,-2,EndDateTime) else end DATEADD(y,-1,PreviousDateTime)

How to SUM and SUBTRACT using SQL?

I think this is what you're looking for. NEW_BAL is the sum of QTYs subtracted from the balance:

SELECT   master_table.ORDERNO,
         master_table.ITEM,
         SUM(master_table.QTY),
         stock_bal.BAL_QTY,
         (stock_bal.BAL_QTY - SUM(master_table.QTY)) AS NEW_BAL
FROM     master_table INNER JOIN
         stock_bal ON master_bal.ITEM = stock_bal.ITEM
GROUP BY master_table.ORDERNO,
         master_table.ITEM

If you want to update the item balance with the new balance, use the following:

UPDATE stock_bal
SET    BAL_QTY = BAL_QTY - (SELECT   SUM(QTY)
                            FROM     master_table
                            GROUP BY master_table.ORDERNO,
                                     master_table.ITEM)

This assumes you posted the subtraction backward; it subtracts the quantities in the order from the balance, which makes the most sense without knowing more about your tables. Just swap those two to change it if I was wrong:

(SUM(master_table.QTY) - stock_bal.BAL_QTY) AS NEW_BAL

SQL query for today's date minus two months

If you are using SQL Server try this:

SELECT * FROM MyTable
WHERE MyDate < DATEADD(month, -2, GETDATE())

Based on your update it would be:

SELECT * FROM FB WHERE Dte <  DATEADD(month, -2, GETDATE())

Calculate exact date difference in years using SQL

Have you tried getting the difference in months instead and then calculating the years that way? For example 30 months / 12 would be 2.5 years.

Edit: This SQL query contains several approaches to calculate the date difference:

SELECT CONVERT(date, GetDate() - 912) AS calcDate
      ,DATEDIFF(DAY, GetDate() - 912, GetDate()) diffDays
      ,DATEDIFF(DAY, GetDate() - 912, GetDate()) / 365.0 diffDaysCalc
      ,DATEDIFF(MONTH, GetDate() - 912, GetDate()) diffMonths
      ,DATEDIFF(MONTH, GetDate() - 912, GetDate()) / 12.0 diffMonthsCalc
      ,DATEDIFF(YEAR, GetDate() - 912, GetDate()) diffYears

---

Related Topics