begin/end dates crystal

[buria]

I am looking for the begin/end dates for the week the user enters a date for. So not the prior week, but the current week. ex:user enters 4/28/04 I want

begin date (sunday) = 4-25-04
end date (saturday) = 5-1-04

I have put together sql to get me this information*below but crystal does not seem to recognize @sunday and @saturday

The parameter field used for the entered date is ?Date. You can see I created a variable called @Date and made it equal to the parameter field the user enters when running the report.

Also, not sure if this code should be a part of my database>sql query or Report>edit selection formula > group

Either way it seems crystal does not recognize @sunday and @saturday

Any tips on how I can handle this?

Thanks



-------------------------------------------------------------------------------------------



DECLARE @SUNDAY DATETIME
DECLARE @SATURDAY DATETIME
DECLARE @Date DATETIME

SET @Date = ?Date


SET @SUNDAY = CASE
when datename(weekday,@Date) = 'MONDAY' THEN
DATEADD(day, -1, @Date)
when datename(weekday,@Date) = 'TUESDAY' THEN
DATEADD(day, -2, @Date)
when datename(weekday,@Date) = 'WEDNESDAY' THEN
DATEADD(day, -3, @Date)
when datename(weekday,@Date) = 'THURSDAY' THEN
DATEADD(day, -4, @Date)
when datename(weekday,@Date) = 'FRIDAY' THEN
DATEADD(day, -5, @Date)
when datename(weekday,@Date) = 'SATURDAY' THEN
DATEADD(day, -6, @Date)
when datename(weekday,@Date) = 'SUNDAY' THEN
@Date
END


SET @SATURDAY = DATEADD(day, 6, @sunday)


select trx_begin_dt, trx_end_dt
from table
where DATEADD(dd, DATEDIFF(dd, 0, trx_begin_dt), 0)between @sunday and @saturday
and resource_code = 'PHOL'
order by trx_begin_dt

 

[Madawc]

If you need the day of the week, that's

DatePart ("w", {date1})

Put this as a formula field and it gives a number, 1 to 7. The default is Sunday as 1 and Saturday as 7, though you can change this using firstDayOfWeek

Does this help?


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Try formulas like:

//{@Sunday}:
{?startdate}-dayofweek({?startdate})+1

//{@Saturday}:
{?startdate}-dayofweek({?startdate})+7

-LB