• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 783
  • Last Modified:

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

0
binaryman101
Asked:
binaryman101
  • 4
  • 3
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now