Hi All, I have a form with some data that users submit and I want to count the number of forms submitted per day in a barChart. My OQL query looks something like this: 'SELECT COUNT(*) as NumberOfForms, FormDate FROM MainModule.myform GROUP BY FormDate' But because the FormDate is a DateTIME field the count is always 1. (because the time is always different for multiple forms on the same day) So how can I do a GROUP BY on Date but not dateTime?? I prefer not to add another datetime to my entity. Anybody with a smart idea on how to solve this??
JanWillem van der Wel
Hi Jan Willem, you should use DATEPART to extract only the date from the DateTime field.
SELECT DATEPART(DAY, "FormDate") AS DayNumber
If time component is not important I’d suggest that to reset the time value when capturing the data once the form is submitted.
That way when you create the chart information it would be displayed as required. For the existing data in the database you would have to run some kind of script to reset only the time component though.