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)
13.10.2023
11:23
MVP
Karol Częczek
In reply to: Maksymilian Stachowiak

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.

Hi

The reason is probably completely different.
You haven't set any message that is returned if the request returns 0, so a system message is returned. that the validation returned 0.
And it returned 0 because your query is missing the current element exclusion. The action is performed in the transaction and also 'sees' the element you are registering.
Add: and WFD_ID <> '{WFD_ID}' , and complete the message to be returned in case of failure.

Regards

Solution (0) Helpful (1) Wrong answer (0)
13.10.2023
11:43
MVP
Maksymilian Stachowiak
In reply to: Karol Częczek

Hi

The reason is probably completely different.
You haven't set any message that is returned if the request returns 0, so a system message is returned. that the validation returned 0.
And it returned 0 because your query is missing the current element exclusion. The action is performed in the transaction and also 'sees' the element you are registering.
Add: and WFD_ID <> '{WFD_ID}' , and complete the message to be returned in case of failure.

Regards

Karol you are right, it seems like cast works with more formats than i thought it would -> https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#date-and-time-styles
Something new I've learned :)

Solution (0) Helpful (0) Wrong answer (0)
15.10.2023
10:18
mshahnawaz
In reply to: Karol Częczek

Hi

The reason is probably completely different.
You haven't set any message that is returned if the request returns 0, so a system message is returned. that the validation returned 0.
And it returned 0 because your query is missing the current element exclusion. The action is performed in the transaction and also 'sees' the element you are registering.
Add: and WFD_ID <> '{WFD_ID}' , and complete the message to be returned in case of failure.

Regards

The help you've given us is much appreciated. Gentlemen, Mr. Karol Czczek, Mr. Maksymilian Stachowiak

The problem seems to have been resolved; I was seemingly missing the WFD_ID <> 'WFD_ID' part of the formula.




Regards,

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