# Filtering with Calendar

**URL:** <https://forum.inductiveautomation.com/t/filtering-with-calendar/4452>\
**Category:** Ignition\
**Created:** [May 11, 2012, 4:49pm UTC](https://forum.inductiveautomation.com/t/filtering-with-calendar/4452 "2012-05-11T16:49:48Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![MiddleC](https://avatars.discourse-cdn.com/v4/letter/m/839c29/32.png) [@MiddleC](https://forum.inductiveautomation.com/u/MiddleC)\
**Post date:** [May 11, 2012, 4:49pm UTC](https://forum.inductiveautomation.com/t/filtering-with-calendar/4452/1 "2012-05-11T16:49:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [May 11, 2012, 10:27pm UTC](https://forum.inductiveautomation.com/t/filtering-with-calendar/4452/2 "2012-05-11T22:27:13Z")

</div>

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}' `

---

<div class="post-metadata">

**Author:** ![MiddleC](https://avatars.discourse-cdn.com/v4/letter/m/839c29/32.png) [@MiddleC](https://forum.inductiveautomation.com/u/MiddleC)\
**Post date:** [May 14, 2012, 4:05pm UTC](https://forum.inductiveautomation.com/t/filtering-with-calendar/4452/3 "2012-05-14T16:05:23Z")

</div>

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?

Thanks…

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [May 14, 2012, 4:07pm UTC](https://forum.inductiveautomation.com/t/filtering-with-calendar/4452/4 "2012-05-14T16:07:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![MiddleC](https://avatars.discourse-cdn.com/v4/letter/m/839c29/32.png) [@MiddleC](https://forum.inductiveautomation.com/u/MiddleC)\
**Post date:** [May 14, 2012, 5:34pm UTC](https://forum.inductiveautomation.com/t/filtering-with-calendar/4452/5 "2012-05-14T17:34:21Z")

</div>

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.