Loop Error

I have the following code in my selection criteria (using CR 10):

Global datetimevar firstDate:= datetime(year(CurrentDateTime), month(CurrentDateTime),1,0,0,0) ;
Global datetimevar lastMonth:= dateadd("m",+0,firstDate);
lastMonth:=dateadd("d",-1, lastMonth);

if {ENG_ORD_MERGE_VW.SHP_DT} = lastMonth
then
Do
lastMonth:=dateadd("d",-1, lastMonth)
While {CTDATE.WRK_DAY} <> 1.00 ;

The report appears to be searching records but then part way through it kicks me out with this error message:

"A loop was evaluated more than the maximum number of times allowed"

What is the maximum number of times a loop can be evaluated? I didn't think there was a limit. Does anyone know how I can write this so that it won't kick me out?You need to increase or decrese {CTDATE.WRK_DAY} in the loop
Otherwise it will result in infinite loop|||I can't increment the WorkDay because all workdays need to equal 1, will it work if I put in a dummy counter, just to increment 1?

looking to collect distinct date part out of datetime field

from this, circdate being a datetime field:
SQLQuery = "select distinct circdate from circdata order by circdate"

I need the distinct date portion excluding the time part.

this has come about when I discovered
I am inserting and updating some datetime values with the same value,
but for some reason, the values are always off by a few seconds. I set
a variable called SetNow assigned to NOW and then set the datetime
fields to this SetNow variable. Then when I collect the distinct date
time I am assuming they will have the same values recorded in
circdate, but no, they are off by several seconds. Makes no sense to me
at all. I tried renaming the variable several times but it makes no
difference at all.
any help appreciated, thanks.SQLQuery = "SELECT distinct CONVERT(char,circdate,1) from circdata"

I think I solved it
any one see a problem with this?
thanks
how does the '1' parameter affect the output as I know there are
several choices|||sdowney717@.msn.com (sdowney717@.msn.com) writes:
> SQLQuery = "SELECT distinct CONVERT(char,circdate,1) from circdata"
> I think I solved it
> any one see a problem with this?
> how does the '1' parameter affect the output as I know there are
> several choices

1 is a format parameter that controls how the datetime value is formatted.
You can read about these in the topic CAST and CONVERT in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||http://www.aspfaq.com/show.asp?id=2464
shows all the outputs
Was wondering though if it wont use an index on a convert.|||sdowney717@.msn.com (sdowney717@.msn.com) writes:
> http://www.aspfaq.com/show.asp?id=2464
> shows all the outputs
> Was wondering though if it wont use an index on a convert.

For the query you gave,

SELECT distinct CONVERT(char,circdate,1) from circdata

this is not an issue. If there is an index on cricdate, SQL Server will
use that index in the most effective, that is to scan the index, because
that is what the query calls for, with or without the convert().

On the other hand

SELECT col1, col2, col3 FROM circdata
WHERE CONVERT(char, circdate, 1) = @.val

will probably not use the index, and in any case the query will not seek
the index, that is lookup the value through the B-tree. This is because
the index is sorted on the datetime value, not on a character value.

To list all rows for a given date you can do:

SELECT col1, col2, col3 FROM circdata
WHERE circdate >= @.val AND circdate < dateadd(DAY, @.val, 1)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Also refer this to know how to query on dates
http://www.karaszi.com/SQLServer/info_datetime.asp

Madhivanan