Solved

date conversion works for field but not selection

Posted on 2014-03-10
4
192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 143

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 143

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

726 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