How to retrieve current month date value

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

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 2-Nov-15 20:39pm

Member 11382784

Updated 2-Nov-15 20:56pm

Sinisa Hajnal

v3

Add a Solution

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.

2 solutions

Add a Solution

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

Tarun Kumar · Answer 1 · 2015-11-02T22:38:00

Solution 1

SELECT GETDATE() AS CurrentDateTime

first try this query

Posted 2-Nov-15 22:38pm

Tarun Kumar

Updated 2-Nov-15 22:43pm

v2

Sanjeev Maurya · Answer 2 · 2015-11-16T02:00:00

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

How to retrieve current month date value

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to retrieve current month date value

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us