Home > User Voice > Datetime field comparision with datetime issue

Datetime field comparision with datetime issue CLOSED
0

12.10.2023
15:59
mshahnawaz

hi
I have datetime type fields i.e From and To ,
i want to check in current db table (WFElements), if datetime exist or not between two dates validating on form submit path using sql query.

Issue: all the time showing validation error message ,

My validation Sample below:

select case
when
(
SELECT count(*)
FROM [BPS_Content_Prod].[dbo].[WFElements]
where wfd_attchoose1='{1189}' and (('{L:1191}' between WFD_AttDateTime1 and WFD_AttDateTime2)
or ('{L:1190}' between WFD_AttDateTime1 and WFD_AttDateTime2)) AND WFD_DTYPEID=17
) > 0
then 0
else 1
end as test

The above query works well in SQL, but issue on the form submit path.



Regards,

13.10.2023
09:30
MVP
Maksymilian Stachowiak
In reply to: mshahnawaz

hi,

As you suggested , i cast the fields as datetime but still the same issue, please see the attachments.



regards,

Hi,

There should be no need to cast WFD_AttDateTimeX, as this column is defined as datetime in the table.
Error comes from the fact, that you are passing value from form, which is not in format, that SQL understands.

Instead of using text of form value - choose ISO - it will provide the value in format, that you should be able to convert do datetime (attached screenshot).

@edit
I've also noted, that you have hard coded column names WFD_AttDateTime1/2 and WFD_DTYPEID=17 - consider using objects pane, pick values/columns from there, to avoid troubles when importing to new environments. In some cases you might end up with same attribute using different column in each environment.

Solution (0) Helpful (0) Wrong answer (0)
Menu
Babinskiego 69
30-393 Krakow, Poland
office@webcon.com
+48 12 443 13 90