| View previous topic :: View next topic |
| Author |
Message |
heymish
General User
Joined: 08 Mar 2007
Posts: 5
Location: Christchurch, New Zealand
|
 Posted: Wed Apr 25, 2007 7:52 pm Post subject: Query Date Between 2 dates |
 |
|
I have 2 tables one with the StartDate and EndDate and another table with a Date. I want to find if the date is between one of the StartDate,EndDate pairs. I have tried 2 different querys which both give me a table not found error.
I have this query
| Code: | | SELECT "Tax"."StartDate", "Tax"."EndDate", "Query_Paid_Total"."Date" FROM "Query_Paid_Total", "Tax" WHERE (("Query_Paid_Total"."Date" > "Tax"."StartDate") AND ("Query_Paid_Total"."Date" < "Tax"."EndDate")) |
For which I get a table not found error
I have also tried
| Code: | | SELECT "Tax"."StartDate", "Tax"."EndDate", "Query_Paid_Total"."Date" FROM "Query_Paid_Total", "Tax" WHERE (("Query_Paid_Total"."Date" BETWEEN "Tax"."StartDate" AND "Tax"."EndDate")) |
for which I also get a talbe not found error.
If I replace Start Date and EndDate with dates rather than table references it works
| Code: | | SELECT "Tax"."StartDate", "Tax"."EndDate", "Query_Paid_Total"."Date" FROM "Query_Paid_Total", "Tax" WHERE (("Query_Paid_Total"."Date" BETWEEN '2006-01-01'AND '2007-01-01')) |
I am not sure if this is also going to be a problem but the dates could fall within more than one date pair.
Anyway to get around this would be nice thanks
Would there be away using datediff
| Code: | | Datediff(d,'Startdate','EndDate') > Datediff(d,'Startdate','Date') |
I just do not see how to add this into the query |
|
| Back to top |
|
 |
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
|
| Back to top |
|
|
heymish
General User
Joined: 08 Mar 2007
Posts: 5
Location: Christchurch, New Zealand
|
| Posted: Thu Apr 26, 2007 11:22 am Post subject: |
|
|
Here it is
| Code: |
SELECT "Query_Paid"."InvoiceNumber", "Query_Invoice_Totals"."Amount", "Query_Paid"."Date" FROM "Query_Invoice_Totals", "Query_Paid" WHERE ( "Query_Invoice_Totals"."InvoiceNumber" = "Query_Paid"."InvoiceNumber" ) |
Is there anything else you would like to see
Does the Query need a nested WHERE to pick out hte Startdate and end date pairs
Last edited by heymish on Thu Apr 26, 2007 11:31 am; edited 1 time in total |
|
| Back to top |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Thu Apr 26, 2007 11:29 am Post subject: |
|
|
no that should be enough. As I recall there was a problem with BETWEEN and dates in the past but I thought that cleared up. Let me try a couple of things here and I'll put something up later this evening.
_________________
Blog - http://baseanswers.spaces.live.com/ |
|
| Back to top |
|
|
|
