| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
01-24-12, 09:30
|
|
Registered User
|
|
Join Date: Nov 2011
Posts: 94
|
|
|
Query not working properly
|
|
So, I have a query that suppose to give me all the records that has Notice Date between today's date and 3 months from now. I think I'm not using the date() function or dateadd() function properly or something but results are all records that are today's date and beyond. All future records are showing. I've also tried BETWEEN Date() And (Date()+90) or something like it too but that didn't work either. Here's my query.
Code:
SELECT ForecastNotice.PropertyID, [Rent & Option].MonthlyRent, [Rent & Option].MonGrossRent, [Rent & Option].LeaseTerm, [Rent & Option].LeaseTermExp, [Rent & Option].LeaseTermNoticeDate, [Rent & Option].OptionToExtend, [Rent & Option].[1stExtNoticeDate], [Rent & Option].[2ndExtNoticeDate], [Rent & Option].[3rdExtNoticeDate], [Rent & Option].[4thExtNoticeDate], [Rent & Option].[5thExtNoticeDate], [Rent & Option].[6thExtNoticeDate], ForecastNotice.NoticeDate
FROM ForecastNotice INNER JOIN [Rent & Option] ON ForecastNotice.PropertyID = [Rent & Option].PropertyID
WHERE (((ForecastNotice.NoticeDate) Between Date() And (DateAdd("m",+3,Date()))));
Thanks.
|
|
01-24-12, 09:35
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
Quote:
Originally Posted by tobabygu
...suppose to give me ... Notice Date between today's date and 3 months from now.
... results ... today's date and beyond. All future records are showing.
|
sounds like you got exactly what you wanted
were you perhaps trying to get dates between 3 months ago and today? historic dates?
|
|
01-24-12, 09:36
|
|
Registered User
|
|
Join Date: Nov 2011
Posts: 94
|
|
|
|
I was trying to get dates between today and 3 months from today but instead I'm getting today, 3 months from today, year from today, 5 years from today etc... 
|
|
01-24-12, 09:48
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
what's the datatype of the NoticeDate column?
|
|
01-24-12, 09:54
|
|
Registered User
|
|
Join Date: Nov 2011
Posts: 94
|
|
Text. Is that the issue? It should be set to date?
|
|
01-24-12, 10:03
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
yes, i believe that's the issue 
|
|
01-24-12, 10:22
|
|
Jaded Developer
|
|
Join Date: Nov 2004
Location: out on a limb
Posts: 9,250
|
|
if you have stored dates in a text / string / char column then make certain you aren't using the same dataa type for numeric columns (assuming you may want to use those columns for any form of mathmatics eg add, sum, subtract and so on OR use those columns in a where clause.
why?
ferinstance storing the numbers 1....13 as string when sorted appear as
1,10,11,12,13,2,3,4,5,6,7,8,9
as opposed to
1,2,3,4,5,6,7,8,9,10,11,12,13
if you use them in a where clause you are doing a string comparison not a numeric comparison.
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton 
|
|
01-24-12, 10:26
|
|
Registered User
|
|
Join Date: Nov 2011
Posts: 94
|
|
Sweetness. Thank y'all. It's working now. 
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Query not working properly
