Filter only if date matches today

binaryman101
binaryman101 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Author

Commented:
Where does that fit into my code?

Author

Commented:
I need this on the notice date .
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Commented:
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

Author

Commented:
It says Invalid Column name - "NoticeDate" now
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Author

Commented:
I am pretty sure this works.  I won't know officially till tomorrow, but it looks correct.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial