Clean method for Current week, Day = Monday

[bmcauliffe2]

This works for getting the Monday of the current week (system time).

Date (Year (CurrentDateTime),1,1)+7*({@weeknumber}-1)-DayOfWeek (Date (Year (CurrentDateTime),1,1))+2

With @weeknumber= int(DatePart("ww",CurrentDate))

Can anyone show me a muh cleaner way of getting the same result please ?

Thanks

 

[goranm]

Hi !

One way to get the monday date is to use the DateAdd function:

Dateadd("d",2,Maximum(LastFullWeek))

/Goran

 

[bmcauliffe2]

I'll give that a go goranm.. thank you for your speedy reply.

 

[synapsevampire]

Not much more elegant, but try:

DATEADD("ww", DATEDIFF("ww",cdate(1900,1,1),currentdate), cdate(1900,1,1))

-k

 

[synapsevampire]

Much simpler goranm, I always forget to leverage CR functions and use old SQL methods - perhaps even:

maximum(lastfullweek)+2

-k

 

[T3leTech]

Reading this thread has been helpful, and I have a similar question. Is there a way (using DateAdd) to specify "yesterday" (like CurrentDate -1) and also a specific time range (like midnight to 8 am)? I've figured out the part about yesterday using a parameter, but cannot figure out how to do the same with time ranges.
Here's what I'm currently using to get "yesterday":

If RunInterval = "Yesterday" then
{DateTimeField} = currentdate -1
else
{DateTimeField} in {StartDateTime} to {EndDateTime}

Thanks in advance for any help!

 

[synapsevampire]

(
If RunInterval = "Yesterday" then
{DateTimeField} = currentdate -1
else
{DateTimeField} in {StartDateTime} to {EndDateTime}
)
and
(
hour({DateTimeField}) < 9
)

This won't pass the SQL though, so I'd create a SQL Expression to create the hour and use it in the record selection formula.

-k

 

[T3leTech]

Thank you for the quick response--that makes perfect sense! I don't quite understand the last part though about &quot;won't pass the SQL&quot;. Can you elaborate on that?

 

[synapsevampire]

If you select Database->Show SQL Query you'll see that the date part of the record selection formula is probably represented in the SQL, but time portion isn't. Hence the time filtering of the record selection will be done within Crystal, as opposed to the database.

Slower performance.

-k

 

[T3leTech]

Is there another way to do this so that the performance is not slow? I am running the report in Seagate Info and the report keeps failing (after two hours!!!) because it times out. Help!