How to retrieve current month date values in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

i have written following query to retrieve user records based on their emi payments

SQL

SELECT 
    PaymentTracker1.*
    ,DATEADD(m,N,StartDate) AS DueDate
    , CASE WHEN GETDATE() > StartDate THEN (Numbers.N - DATEDIFF(m,StartDate,GETDATE())) * MonthlyPay ELSE Numbers.N * MonthlyPay END AS DueAmount
FROM 
    PaymentTracker1 
    INNER JOIN Numbers on DATEDIFF(m,PaymentTracker1.StartDate,PaymentTracker1.EndDate) >= Numbers.N where DueDate is current date
ORDER BY
    PaymentTracker1.Name
    ,Numbers.N

by using this query all records are displaying ..out put image of that query is click below link

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

in that i want to display only this record(only current month record if it is crossed then show next month record )

49 Ajay Merugu Chit 200000 11000 24 09/01/2015 09/01/2017 99893201276 South Swaroop Nagar Uppal Hyderabad 2015-10-01 00:00:00.000 0

Posted 27-Oct-15 19:34pm

Member 11382784

Updated 27-Oct-15 19:50pm

Suvendu Shekhar Giri

v2

Add a Solution

2 solutions

Solution 2

SELECT DATEPART(MM,GETDATE())

this query will be more specific to get the current month

Posted 29-Oct-15 3:31am

vinay kumar0164

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

**Debojyoti Saha** · Accepted Answer · 2015-10-27T20:25:00

Solution 1

Use The Code

SQL

where datepart(m,DueDate)= datepart(m,getdate()) AND datepart(yy,DueDate)= datepart(yy,getdate())

Posted 27-Oct-15 20:25pm

Debojyoti Saha

Updated 27-Oct-15 23:01pm

v3

Comments

Member 11382784 28-Oct-15 2:45am

Thanks for your help sir..
am getting error
Invalid column name 'DueDate'.

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(m,DueDate)= datepart(m,getdate())
ORDER BY
PaymentTracker1.Name
,Numbers.N

Debojyoti Saha 28-Oct-15 3:25am

You have already mentioned the "DueDate" at above code.
If your "DueDate" is DATEADD(m,N,StartDate) AS DueDate
then use the DATEADD(m,N,StartDate) only into the DatePart

Debojyoti Saha 28-Oct-15 3:30am

where datepart(m,yourDateColoumn)= datepart(m,getdate())

Member 11382784 28-Oct-15 3:38am

2 dates are showing sir that is oct 2015 and oct 2016

Debojyoti Saha 28-Oct-15 3:57am

then you need this query
where datepart(m,yourDateColoumn)= datepart(m,getdate()) AND datepart(y,yourDateColoumn)= datepart(y,getdate())

Member 11382784 28-Oct-15 3:40am

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

find the out put image sir

Debojyoti Saha 28-Oct-15 4:02am

then you need this query
where datepart(m,yourDateColoumn)= datepart(m,getdate()) AND datepart(y,yourDateColoumn)= datepart(y,getdate())

Member 11382784 28-Oct-15 4:10am

no errors sir.. but 0 records showing

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(m,DATEADD(m,N,StartDate))= datepart(m,getdate()) AND datepart(y,DATEADD(y,N,StartDate))= datepart(y,getdate())
ORDER BY
PaymentTracker1.Name
,Numbers.N

Debojyoti Saha 28-Oct-15 5:01am

Sorry for the Code. Please use yy instead of y on DatePart

Member 11382784 28-Oct-15 5:25am

again am getting 2 dates sir 2015 and 2016

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(m,DATEADD(m,N,StartDate))= datepart(m,getdate()) AND datepart(yy,DATEADD(y,N,StartDate))= datepart(yy,getdate())
ORDER BY
PaymentTracker1.Name
,Numbers.N

Debojyoti Saha 28-Oct-15 5:44am

why you are using DATEADD(m,N,StartDate), DATEADD(y,N,StartDate)
DATEADD(m,N,StartDate) increase a month and
DATEADD(y,N,StartDate) increase a year
I don't know your search criteria but i think it should be only StartDate

Member 11382784 28-Oct-15 6:14am

overview of page is
i have following table in database please find the picture, click below link

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

i want payment update for this table
37 Chintala Naveen Sagar Chit 100000 9900 12 08/01/2015 08/01/2016 9985437775 Karwan,Mehidipatnam

i want to bind this values to grid and show every month payment and due amount , if i update payment show value 0 , when a month crossed again show monthly pay as due amount, if he not paid money for 3 months show as 3*monthlpay.

Maciej Los 29-Oct-15 10:19am

5ed!