4 Replies - 560 Views - Last Post: 08 October 2009 - 07:45 PM Rate Topic: -----

#1 adub666  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 26-September 09

Query Issues

Posted 08 October 2009 - 03:58 PM

Hello,

I am creating a query that will find the top 5 due dates for bills and order them from by the bill that is due the soonest. The query that I am using is :

SELECT TOP 5 Bill,DueDate FROM Expenses WHERE DATEVALUE(DueDate) >= DATE() ORDER BY DueDate ASC


I am displaying the results in a datagridview box in vb.net 2008. I guess what i want to be able to do, is query based on the month and day without the year playing a factor in it.

Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: Query Issues

#2 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10596
  • View blog
  • Posts: 39,258
  • Joined: 27-December 08

Re: Query Issues

Posted 08 October 2009 - 04:38 PM

Try this:

SELECT Top 5 
FROM(
	 SELECT Bill, DueDate
	 FROM EXPENSES
	 ORDER BY DueDate ASC);


Was This Post Helpful? 0
  • +
  • -

#3 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 267
  • View blog
  • Posts: 1,477
  • Joined: 07-April 08

Re: Query Issues

Posted 08 October 2009 - 06:51 PM

I would do something along the lines of:


SELECT TOP 5 Bill
					 ,DueDate
FROM 
			EXPENSES
WHERE 
			DATEPART(MONTH,DueDate) >= DATEPART(MONTH,GETDATE())
			AND DATEPART(DAY,DueDate) >= DATEPART(DAY,GETDATE())



Was This Post Helpful? 0
  • +
  • -

#4 adub666  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 26-September 09

Re: Query Issues

Posted 08 October 2009 - 07:00 PM

Awesome, Thank you guys very much! I forgot to mention that it was in access, I dont think GETDATE() is compatible. Any alternatives to that??
Was This Post Helpful? 0
  • +
  • -

#5 adub666  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 26-September 09

Re: Query Issues

Posted 08 October 2009 - 07:45 PM

Hey guys,

Quick update, I started playing around with the 2nd query posted:

SELECT DueDate
FROM Expenses 
WHERE 
DATEPART("m",DueDate) >= DATEPART("m",DATE())
AND
DATEPART("d",DueDate) >= DATEPART("d",DATE())
ORDER BY DueDate ASC


It seems to be working, It just is only showing values in the current month. I am no SQL master, but to me the code logic makes sense, as well as the statements used.

another option that would possibly be useful to what I am trying to do would be to maybe update the month after it passes.

so for example:


10-8-2009 would become
11-8-2009 then
12-8-2009 then
1-8-2010

that actually would prob make the most sense for what I am doing.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1