Filter only if date matches today

I have a simple query that I have a Alias field of NoticeDate and I would like ot filter this query and only produce results if the date equals today.  The data in the NoticeDate column now appears as 11/18/2008 12:00:00 AM.  This data could be anytime during the day.  But if it is 11/18/2008 any time I would like it to produce results.
SELECT     Telebill.dbo.Accounts.AccountNumber, Telebill.dbo.Accounts.CustomerName, Telebill.dbo.Invoices.DueDate - 7 AS NoticeDate, 
                      Telebill.dbo.Invoices.DueDate, Telebill.dbo.Accounts.EmailAddress, Telebill.dbo.AccountBalances.CurrentBalance, 
                      Telebill.dbo.Accounts.AccountStatus
FROM         Telebill.dbo.AccountBalances INNER JOIN
                      Telebill.dbo.Accounts ON Telebill.dbo.AccountBalances.AccountNumber = Telebill.dbo.Accounts.AccountNumber INNER JOIN
                      Telebill.dbo.Invoices ON Telebill.dbo.Accounts.AccountNumber = Telebill.dbo.Invoices.AccountNumber
WHERE     (Telebill.dbo.Accounts.EmailAddress IS NOT NULL) AND (Telebill.dbo.AccountBalances.CurrentBalance > 1.00) AND 
                      (Telebill.dbo.Accounts.AccountStatus = N'NORMAL') AND (Telebill.dbo.Accounts.ReportGroup LIKE N'%U4%') AND 
                      (Telebill.dbo.Invoices.DueDate > GETDATE())

Open in new window

binaryman101Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
 (Telebill.dbo.Invoices.DueDate >=  CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120)  )
AND  (Telebill.dbo.Invoices.DueDate < DATEADD(DAY, 1, CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120))  )

Open in new window

0
binaryman101Author Commented:
Where does that fit into my code?
0
binaryman101Author Commented:
I need this on the notice date .
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry.
SELECT     Telebill.dbo.Accounts.AccountNumber, Telebill.dbo.Accounts.CustomerName, Telebill.dbo.Invoices.DueDate - 7 AS NoticeDate, 
                      Telebill.dbo.Invoices.DueDate, Telebill.dbo.Accounts.EmailAddress, Telebill.dbo.AccountBalances.CurrentBalance, 
                      Telebill.dbo.Accounts.AccountStatus
FROM         Telebill.dbo.AccountBalances INNER JOIN
                      Telebill.dbo.Accounts ON Telebill.dbo.AccountBalances.AccountNumber = Telebill.dbo.Accounts.AccountNumber INNER JOIN
                      Telebill.dbo.Invoices ON Telebill.dbo.Accounts.AccountNumber = Telebill.dbo.Invoices.AccountNumber
WHERE     (Telebill.dbo.Accounts.EmailAddress IS NOT NULL) AND (Telebill.dbo.AccountBalances.CurrentBalance > 1.00) AND 
                      (Telebill.dbo.Accounts.AccountStatus = N'NORMAL') AND (Telebill.dbo.Accounts.ReportGroup LIKE N'%U4%') AND 
                      (Telebill.dbo.Invoices.NoticeDate > GETDATE()
AND (Telebill.dbo.Invoices.DueDate >=  CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120)  )
AND  (Telebill.dbo.Invoices.NoticeDate < DATEADD(DAY, 1, CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120))  ))

Open in new window

0
St3veMaxCommented:
I think this should also do the trick.

HTH
SELECT     
	Telebill.dbo.Accounts.AccountNumber, 
	Telebill.dbo.Accounts.CustomerName, 
	Telebill.dbo.Invoices.DueDate - 7 AS NoticeDate, 
	Telebill.dbo.Invoices.DueDate, 
	Telebill.dbo.Accounts.EmailAddress, 
	Telebill.dbo.AccountBalances.CurrentBalance, 
         Telebill.dbo.Accounts.AccountStatus
FROM
	Telebill.dbo.AccountBalances 
INNER JOIN
	Telebill.dbo.Accounts ON Telebill.dbo.AccountBalances.AccountNumber = Telebill.dbo.Accounts.AccountNumber 
INNER JOIN
	Telebill.dbo.Invoices ON Telebill.dbo.Accounts.AccountNumber = Telebill.dbo.Invoices.AccountNumber
WHERE     
	(Telebill.dbo.Accounts.EmailAddress IS NOT NULL) 
		AND 
	(Telebill.dbo.AccountBalances.CurrentBalance > 1.00) 
		AND 
	(Telebill.dbo.Accounts.AccountStatus = N'NORMAL') 
		AND 
	(Telebill.dbo.Invoices.DueDate = CONVERT(Varchar(8), GetDate(), 112))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
binaryman101Author Commented:
It says Invalid Column name - "NoticeDate" now
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see
>(Telebill.dbo.Invoices.DueDate - 7 as NoticeDate)


SELECT     Telebill.dbo.Accounts.AccountNumber, Telebill.dbo.Accounts.CustomerName, Telebill.dbo.Invoices.DueDate - 7 AS NoticeDate, 
                      Telebill.dbo.Invoices.DueDate, Telebill.dbo.Accounts.EmailAddress, Telebill.dbo.AccountBalances.CurrentBalance, 
                      Telebill.dbo.Accounts.AccountStatus
FROM         Telebill.dbo.AccountBalances INNER JOIN
                      Telebill.dbo.Accounts ON Telebill.dbo.AccountBalances.AccountNumber = Telebill.dbo.Accounts.AccountNumber INNER JOIN
                      Telebill.dbo.Invoices ON Telebill.dbo.Accounts.AccountNumber = Telebill.dbo.Invoices.AccountNumber
WHERE     (Telebill.dbo.Accounts.EmailAddress IS NOT NULL) AND (Telebill.dbo.AccountBalances.CurrentBalance > 1.00) AND 
                      (Telebill.dbo.Accounts.AccountStatus = N'NORMAL') AND (Telebill.dbo.Accounts.ReportGroup LIKE N'%U4%') AND 
                      (Telebill.dbo.Invoices.NoticeDate > GETDATE()
AND (Telebill.dbo.Invoices.DueDate >=  DATEADD(DAY, 7 CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120))  )
AND  (Telebill.dbo.Invoices.DueDate < DATEADD(DAY, 8, CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120))  ))

Open in new window

0
binaryman101Author Commented:
I am pretty sure this works.  I won't know officially till tomorrow, but it looks correct.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.