How do I pass a date range to the server? 3

[sewilj]

I am trying to pass the following to the SQL v7 server

IF CurrentDate > date(year(currentdate),04,01)
then {CPCASE.DOFF} in date(year(currentdate),04,01) to date(year(currentdate)+1,03,31)
else {CPCASE.DOFF} in date(year(currentdate)-1,04,01) to date(year(currentdate),03,31)

{CPCASE.DOFF} is my date field

Whenever I 'Show SQL Query...' there is no mention of the data range to query on. Therefore, from what I can see it brings back every record to sort on my PC. I have been trying to use case logic in the select expert to pass the date range to the server. I am using

select {CPCASE.DOFF}
case CurrentDate > date(year(currentdate),04,01) : {CPCASE.DOFF} in date(year(currentdate),04,01) to date(year(currentdate)+1,03,31)
default : {CPCASE.DOFF} in date(year(currentdate)-1,04,01) to date(year(currentdate),03,31)

but keep finding that a date-time is required at the first currentdate. I have tried creating a date formula which just contains 'currentdate', and using this in place of currentdate - but still the same results.

Am I fighting a loosing battle with this?


Lewis
United Kingdom

 

[rhinok]

One of the simplest ways to accomplish your goal is to use the IIF statement:

{CPCASE.DOFF} In
IIF
(
CurrentDate > Date(Year(CurrentDate),04,01),//Condition
Date(Year(CurrentDate),04,01) To Date(Year(CurrentDate)+1,03,31),//If True
Date(Year(CurrentDate)-1,04,01) To Date(Year(CurrentDate),03,31)//If False
)

This returns the following in Show SQL Query if you convert DateTimes to Dates:

WHERE
CPCASE."DOFF" >= {ts '2003-04-01 00:00:00.00'} AND
CPCASE.&quot;DOFF&quot; <= {ts '2004-03-31 23:59:59.00'}

Or the following if you leave DateTimes as DateTimes:

WHERE
CPCASE.&quot;DOFF&quot; >= {ts '2003-04-01 00:00:00.00'} AND
CPCASE.&quot;DOFF&quot; < {ts '2004-04-01 00:00:59.00'}

 

[sewilj]

rhinok

Thanks for the advice. This has now quickened my report a great deal. Now i'll just go away and study how IIF is different to IF statements.

Thanks again

Lewis
United Kingdom

 

[synapsevampire]

Your IF statement probably failed because you didn't pass the times as well, and you didn't fully qualify both sides of the IF.

The IIF above does a better job in this instance, but eventually fails in significantly complicated record selection formulas.

If you generate an else if clause you'll have better results, though put in the times as well.

I have a FAQ which addresses passing SQL:

faq767-3825

-k

 

[sewilj]

Synapsevampire

Thanks for the above. I have taken a look at the FAQ and this will be very helpful.

Thanks

Lewis
United Kingdom