Compare date (urgent) 1

[NEL3644]

Hi,
I want to build a query that finds current date (system date) and then compare to the dates on the table so that it only capture the next 12 dates including the current date.
I have a table called Dates that looks like this:

Dates

Startdate Enddate
-----------------------------
jan, 23 1996 | jan, 25 1996
Mar, 05 1996 | Mar, 07 1996
Apr, 23 1996 | Apr, 28 1996
jan, 23 2000 | jan, 26 2000
Mar, 05 2000 | Mar, 06 2000
Sep, 20 2000 | Sep, 21 2000
Sep, 26 2000 | Sep, 28 2000 -----Current date
Oct, 05 2000 | Oct, 09 2000
Oct, 23 2000 | Oct, 27 2000
Oct, 30 2000 | Nov, 02 2000
Nov, 20 2000 | Nov, 22 2000
Nov, 23 2000 | Nov, 24 2000
jan, 23 2001 | jan, 25 2001
Mar, 05 2001 | Mar, 06 2001
Apr, 23 2001 | Apr, 29 2001
Aug, 23 2002 | Aug, 25 2002
Mar, 05 2002 | Mar, 10 2002
Apr, 23 2002 | Apr, 26 2002 -------12th date
jan, 23 2002 | jan, 25 2002
Mar, 05 2002 | Mar, 12 2002
Apr, 23 2003 | Apr, 30 2003
Aug, 23 2003 | Aug, 28 2003



Thank you very much!
[sig][/sig]

 

[foxdev]

I'm going to take a stab at it:

[tt]select top 12 * from MyTable where enddate between getdate() and DATEADD(day, 12, enddate) order by enddate[/tt]

I probably missed something somewhere, but I think this will come close to what you want. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[karluk]

Robert has a very good suggestion, but based on your data the next 12 enddates could be over a year in the future. I would try

select top 12 * from MyTable where enddate >= getdate() order by enddate [sig][/sig]

 

[foxdev]

Karluk is right in identifying that, because we're already specifying TOP 12, the upper range of the WHERE clause is unnecessary. Good catch. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[NEL3644]

Your help was indeed very valuable, but let me rephrase something here...

I want to capture ONLY the dates between &quot;CURRENT DATE&quot; and the &quot;12th RECORD&quot;...When you specify TOP 12 in the query I get the first 12 records not 12 records starting from the &quot;CURRENT DATE&quot;...Again, thank you very much for your cooperation FoxDev and karluk. [sig][/sig]

 

[karluk]

I had not heard of the &quot;top 12&quot; syntax before reading Robert's suggestion, so I also was a little suspicious that it might not select records starting from the current date. However, it does seem to work. The key is the 'order by enddate' clause. If you remove that clause it gives unpredictable results. [sig][/sig]

 

[foxdev]

Most definitely. While syntactically ORDER BY is not required with a TOP statement, in practice you'd never want to have a TOP statment without an ORDER BY. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[NEL3644]

I'm doing exactly what you've suggested me, but for some reason, the query is still capturing the first 12th records not the records starting from &quot;CURRENT DATE&quot; up to the &quot;12th date&quot; which is what I want the query to capture after all...
I was thinking that maybe because I'm using SQL in ColdFusion studio, the syntax you guys have been helping me w/ needs some changes...Any coments about it?
Let me show my query in ColdFusion environment and you tell me if there is anything that I'm unware of...

<CFQUERY NAME=&quot;GetSessions&quot; DATASOURCE=&quot;#Application.DataSource#&quot; DBTYPE=&quot;ODBC&quot;>
SELECT TOP 12 * FROM Dates WHERE ProgramID=#ProgramID#
AND EndDate >= #CreateODBCDate(now())# Order by EndDate
</CFQUERY> [sig][/sig]

 

[karluk]

I know nothing about ColdFusion (although it sounds like a great name for a product) so I won't be any help in environment specific advice. However the symptoms you're seeing are consistent with #CreateODBCDate(now())# not evaluating to the current date. If all enddates in the table are greater than the value of #CreateODBCDate(now())# you would see the first 12 rows.

Can you test this? For example if you temporarily replace #CreateODBCDate(now())# with a literal date like '09/28/2000' you should see the data you are expecting. That would narrow down your problem to the value of the date variable rather than some weird quirk in the way the sql is executed. [sig][/sig]