It is currently Mon Sep 01, 2014 5:50 pm




Post new topic Reply to topic  [ 5 posts ] 
 Filtering with Calendar 
Author Message
Trooper
Trooper

Joined: Wed Sep 21, 2011 1:01 pm
Posts: 12
Post 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.


Fri May 11, 2012 8:49 am
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 2:46 pm
Posts: 1982
Location: Sacramento, CA
Post Re: Filtering with Calendar
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:
Code:
SELECT ... FROM table
WHERE t_stamp >= '{Root Container.Calendar.WeekBegin}' AND
t_stamp <= '{Root Container.Calendar.WeekEnd}'

_________________
Travis Cox
Inductive Automation
Technical Support Rep.


Fri May 11, 2012 2:27 pm
Profile WWW
Trooper
Trooper

Joined: Wed Sep 21, 2011 1:01 pm
Posts: 12
Post Re: Filtering with Calendar
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...


Mon May 14, 2012 8:05 am
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 2:46 pm
Posts: 1982
Location: Sacramento, CA
Post Re: Filtering with Calendar
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.

_________________
Travis Cox
Inductive Automation
Technical Support Rep.


Mon May 14, 2012 8:07 am
Profile WWW
Trooper
Trooper

Joined: Wed Sep 21, 2011 1:01 pm
Posts: 12
Post Re: Filtering with Calendar
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.


Mon May 14, 2012 9:34 am
Profile
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 


Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: