Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have written a query to retrieve current month value from start date and end date. and am getting current month as well as next year value also returning

this is my query
SQL
SELECT 
    PaymentTracker1.*
    , convert(varchar(20),DATEADD(m,N,StartDate),110) AS DueDate
    ,(Numbers.N * MonthlyPay) AS DueAmount
FROM 
    PaymentTracker1 
    INNER JOIN Numbers on DATEDIFF(m,PaymentTracker1.StartDate,PaymentTracker1.EndDate) >= Numbers.N 
    
    where  datepart(mm,DATEADD(mm,N,StartDate))= datepart(mm,getdate()) and  datepart(yy,DATEADD(y,N,StartDate))= datepart(yy,getdate())
ORDER BY
    PaymentTracker1.Name
    ,Numbers.N


the output image of query is click below link

http://i.stack.imgur.com/LMg1m.png

am getting next year month value also,

how to return only current month and year value
Posted
Updated 2-Nov-15 20:56pm
v3
Comments
Krunal Rohit 3-Nov-15 2:53am    
In WHERE clause compare your date with the Current Date i.e GETDATE().

-KR
Sinisa Hajnal 3-Nov-15 3:02am    
What are you trying to do? You're adding months to start date and trying to match them to current month AND adding years to start date trying to match that to current year...I'm confused.

SELECT GETDATE() AS CurrentDateTime


first try this query
 
Share this answer
 
v2
use this one


SQL
DECLARE @mydate DATETIME, @starDate datetime, @EndDate datetime
SELECT @mydate = GETDATE()

set @starDate = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101))

set @EndDate =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101))


SELECT
    PaymentTracker1.*
    , convert(varchar(20),DATEADD(m,N,StartDate),110) AS DueDate
    ,(Numbers.N * MonthlyPay) AS DueAmount
FROM
    PaymentTracker1
    INNER JOIN Numbers on DATEDIFF(m,PaymentTracker1.StartDate,PaymentTracker1.EndDate) >= Numbers.N

    where  StartDate>=@starDate and StartDate<@EndDate
ORDER BY
    PaymentTracker1.Name
    ,Numbers.N
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900