Filtering with Calendar

I’m using the calendar component for day, week, month, and year queries. The day query works properly. But, the others (e.g. week) return rows from incorrect months–the days are correct from all months.

I use dynamic propertys (strings) to determine week begin and week end dates depending on day selected on calendar. Formatting is dd/MM/yyyy.

SQL expression is: SELECT FROM
WHERE Convert(Varchar(10), t_stamp, 103) >= {Root Container.Calendar.WeekBegin} AND
Convert(Varchar(10), t_stamp, 103) <= {Root Container.Calendar.WeekEnd}

The t_stamp field is type datetime.

I would format the dates like this:

yyyy-MM-dd HH:mm:ss

which happens automatically from date objects. Your query will look something like:SELECT ... FROM table WHERE t_stamp >= '{Root Container.Calendar.WeekBegin}' AND t_stamp <= '{Root Container.Calendar.WeekEnd}'

I’ll try that, althought I was attempting to eliminate the time portion. Should be okay for week, month, and year queries but not days?

Also, I’m assuming the dynamic property should be date types?


You can keep the time in but force the minutes to 00:00:00 or 23:59:59 for start of day and end of day.

The problem I have when keeping the time portion is pulling only 1 record for each shift/day. I’m using the Max() to sort through records with the same date. So, for week queries I’d like the max footage records for that day & shift.