I have created 2 structures, structure A and B
These 2 structures have just one text field each, location and identifier, and are used as 'editable lists' for dropdowns in structures C and D
Using a hidden field in structure C, which I then named "Type" and gave it a default value of "Accidents", I then created a SQL query in a visualizer which returns the count of how many entries have been submitted using Form for structure C. (How many Accidents)
I would like to create another visualizer which returns the number of entries that have been submitted during the current month only.
As I understand, the submitted information from the Forms is kept in the table LiveForms_SubmissionInfo
But I cannot see how I could make a SQL query to work where it returns entries in the current month, such like as follows:
SELECT COUNT(*) FROM LiveForms_SubmissionInfo WHERE FieldValue='Accident' AND
MONTH(FieldValue) = MONTH(dateadd(dd, -1, GetDate()))
AND
YEAR(FieldValue) = YEAR(dateadd(dd, -1, GetDate()));
I get the error: "Conversion failed when converting date and/or time from character string"
Any help or advice would be greatly appreciated.