SQLTeam.com | Weblogs | Forums

Today date -1 year

Hello
I have sql 2008 r2 and first I have to get today's date to calculate the total sales of the month
Example getdate(now) this results on ´2019-09-18´ so I need to get the total sales of the mont (Sep-01 to Sep-18) but all this for the last year so I need a formula to get all the invoices from Sep-01-2019 to Sep-18-2019 to get the total sale of September

Something Like this :
SELECT Sales
FROM SalesTable
WHERE YEAR(InvoiceDate) = YEAR(GETDATE())-1 AND MONTH(InvoiceDate) = MONTH(GETDATE()) AND DAY(InvoiceDate)<=DAY(GETDATE())

Thanks in advance

hi

image

select 
     * 
from 
    @Sample_data 
where  
    InvoiceDate 
	   between
           dateadd(year,-1,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) -- 1st of sept  last year 
              and 
           dateadd(dd,17, dateadd(year,-1,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))) -- 18th of sept last year
please click arrow to the left for Drop Create Sample Data
declare @Sample_data table (InvoiceDate datetime)

insert into @Sample_data values 
('2019-09-12'),
('2019-09-13'),
('2019-09-01'),
('2019-09-18'),
('2019-09-25'),
('2019-08-25')

select 'Sample Data' , * from @Sample_data
3 Likes

Thanks a lot