Solved

date conversion works for field but not selection

Posted on 2014-03-10
4
190 Views
Last Modified: 2014-03-10
If I use the code dateadd in the select statement, it works fine.   But every time I try and include it in the where statement I get no records.   I've confirmed that I have 211 records that should be pulled for April 2014.    I don't see the issue below... feel like I'm missing something!   (I put the dateadds in the select statement just to prove to myself that is the correct format.)

(SELECT        GiftFactID, SUM(InstallmentBalance) AS currentdue, InstallmentDate, DATEADD(month, 1, InstallmentDate) ,  DATEADD(month, 1, GETDATE())

FROM            FACT_GiftInstallment

WHERE DATEpart(month, InstallmentDate) = DATEADD(month, 1, GETDATE()) and
(DATEPART(year, InstallmentDate) = DATEPART(year, GETDATE()))
AND                  (InstallmentBalance > 0)

GROUP BY GiftFactID, InstallmentDate)
0
Comment
Question by:cindyfiller
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39917512
there IS an error in the query, as you don't really compare the right things.
let's see if this works better:
WHERE DATEPART(month, InstallmentDate) = datepart(month, DATEADD(month, 1, GETDATE()) ) 
and DATEPART(year, InstallmentDate) = DATEPART(year, dateadd(MONTH, 1, GETDATE()))
AND InstallmentBalance > 0 

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39917516
0
 

Author Comment

by:cindyfiller
ID: 39917528
I didn't realize I'd have to do both the datepart and the dateadd - but it definitely works.  I'll read up on the article you referenced.  Thank you so much!
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39917539
Hello,

What do you really want to do?
DATEpart(month, InstallmentDate) -> will give you month number of InstallmentDate.
DATEADD(month, 1, GETDATE()) -> will give you DATE after addinng 1 month to current date, which will be something like '04/10/2014 7:01:00 AM', and both cannot be equated.

You may want to use below code:
DatePart(month, InstallmentDate) = DatePart(month(DATEADD(month, 1, GETDATE()))
AND DATEPART(year, InstallmentDate) = DATEPART(year, DATEADD(month, 1, GETDATE()))

Open in new window

or simply:
Month(InstallmentDate) = Month(DATEADD(month, 1, GETDATE()))
AND Year (InstallmentDate) = Year (DATEADD(month, 1, GETDATE()))

Open in new window


-Harish
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question