I'm having a screen with a date on it. The date in SQL is formatted yyyy-MM-dd. However when I need to search in the screen, I need to format it that way.
Is there a way to make sure the user can put in dd-MM-yyyy and the search will be good?
On date:
On database date
I tried a calculated field, however that search cannot make use of my index on date, so that won't perform good.
Best answer by Mark Jongeling
An alternative could be a persisted calculated column that saves the date in the NL format, but this then only functions for used using the NL-culture for dates.
You can use this as value for the calculated field; replace sysdatetime() with the date field you would like to convert:
convert(varchar, sysdatetime(), 105) PERSISTEDI believe the GUI does a LIKE operation when using the combined filter. This will cause the query to not make use of any index present on the table to my knowledge. The performance can be increased by having less columns inside the combined filter as it will do the LIKE operation for each columns available in the combined filter.
+23Hi Peter,
The combined filter does not convert the date as how the field shows it as the combined filter input is used to create a SQL-query to filter the data. What you could to is create a (hidden) expression field with datatype varchar that converts the date to how you would like to search.
You can use this as value for the expression field; replace sysdatetime() with the date field you would like to convert:
select convert(varchar, sysdatetime(), 105)
That's not a good solution, as I have a very large table where I have an index on date. This won't let me use the index and forces a table scan. Which will be very slow for the user.
+23An alternative could be a persisted calculated column that saves the date in the NL format, but this then only functions for used using the NL-culture for dates.
You can use this as value for the calculated field; replace sysdatetime() with the date field you would like to convert:
convert(varchar, sysdatetime(), 105) PERSISTEDI believe the GUI does a LIKE operation when using the combined filter. This will cause the query to not make use of any index present on the table to my knowledge. The performance can be increased by having less columns inside the combined filter as it will do the LIKE operation for each columns available in the combined filter.
True, it still is a like. I think I will disable the combined filter on this column and use the 'normal’ filter for it.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.