CODEWITHSUNDEEP
sqlsql-serverdatedatetime
user3384781
user3384781

Reputation: 1

MSSQL Query to get value between 1 month and a fixed day

I can't managed to do it yet.

This is the idea:

select sum(amount) 
from  account
where amountdate >= 'CURRENTSYSTEMYEAR/CURRENTSYSTEMMONTH/10'  
   and amountdate <= 'CURRENTSYSTEMYEAR(+1 if it's december) / CURRENTSYSTEMMONTH + 1/10'

I know date queries don't work that way so I've using datepart and dateadd but didn't managed to do it.

As an example, if it is March 5, 2014, I want my date range to be March 10, 2014 to April 10, 2014.

Upvotes: 0

Views: 135

Answers (2)

Dipendu Paul
Dipendu Paul

Reputation: 2753

SELECT SUM(amount) 
FROM  account
WHERE amountdate >= CAST(YEAR(GETDATE()) AS CHAR(4))+'/'+CAST(MONTH(GETDATE()) AS CHAR(2))+'/10' 
   and amountdate <= DATEADD(MM,1, CAST(YEAR(GETDATE()) AS CHAR(4))+'/'+CAST(MONTH(GETDATE()) AS CHAR(2))+'/10' )

Upvotes: 0

Jason Goemaat
Jason Goemaat

Reputation: 29204

You can convert to string and back to date:

select sum(amount)
from account
where amountdate >= convert(datetime, convert(varchar(8), getdate(), 120) + '10')
    and amountdate <= dateadd(month, 1, convert(datetime, convert(varchar(8), getdate(), 120) + '10'))

Here are the values:

select convert(datetime, convert(varchar(8), getdate(), 120) + '10')
-- 2014-03-10 today (2014-03-05)

select dateadd(month, 1, convert(datetime, convert(varchar(8), getdate(), 120) + '10'))
-- 2014-04-10 today (2014-03-05)

Upvotes: 1

Related Questions