Bug #2242
Message "invalid statement: STR_TO_DATE" when using MS SQL
30%
Description
I activate Extended Fields 0.2.2 on Redmine 2.3.3.
Our installation of Redmine is on CentOS, uses MS SQL server 2008 and Ruby on rails 2.0.0-p247
When I click on the Calendar tab of a project, I get an internal error, with the following indication in the log:
ActiveRecord::StatementInvalid (TinyTds::Error: 'STR_TO_DATE' is not a recognized built-in function name.: EXEC sp_executesql N'SELECT [custom_values].[id] AS t0_r0, [custom_values].[customized_type] AS t0_r1, [custom_values].[customized_id] AS t0_r2, [custom_values].[custom_field_id] AS t0_r3, [custom_values].[value] AS t0_r4, [custom_fields].[id] AS t1_r0, [custom_fields].[type] AS t1_r1, [custom_fields].[name] AS t1_r2, [custom_fields].[field_format] AS t1_r3, [custom_fields].[possible_values] AS t1_r4, [custom_fields].[regexp] AS t1_r5, [custom_fields].[min_length] AS t1_r6, [custom_fields].[max_length] AS t1_r7, [custom_fields].[is_required] AS t1_r8, [custom_fields].[is_for_all] AS t1_r9, [custom_fields].[is_filter] AS t1_r10, [custom_fields].[position] AS t1_r11, [custom_fields].[searchable] AS t1_r12, [custom_fields].[default_value] AS t1_r13, [custom_fields].[editable] AS t1_r14, [custom_fields].[visible] AS t1_r15, [custom_fields].[multiple] AS t1_r16, [custom_fields].[is_for_new] AS t1_r17, [custom_fields].[hint] AS t1_r18 FROM [custom_values] LEFT OUTER JOIN [custom_fields] ON [custom_fields].[id] = [custom_values].[custom_field_id] WHERE (custom_fields.field_format = N''date'' AND custom_values.customized_type = N''Issue'' AND custom_values.customized_id IN (NULL) AND STR_TO_DATE(custom_values.value, ''%Y-%m-%d'') BETWEEN ''09-30-2013'' AND ''11-03-2013'')')Removing the calls to STR_TO_DATE() in /lib/extended_calendars_controller_patch.rb fixes the internal error issue (but it might of course cause other issues, this is not a fix, just a check that the issue lies there and only there).
Associated revisions
Disabled calendar changes for non-MySQL installations (#2242)
History
#1
Updated by Dean Serenevy about 11 years ago
Updated by The PostgreSQL backend has the same issue and I solved it in the same way, replaced:
STR_TO_DATE(#{CustomValue.table_name}.value, '%Y-%m-%d')with
#{CustomValue.table_name}.valuein three places.
#2
Updated by Andriy Lesyuk over 10 years ago
Updated by - Status changed from New to Open
- Target version set to 0.2.3
#3
Updated by Andriy Lesyuk over 10 years ago
- Status changed from Open to In Progress
- % Done changed from 0 to 30
Dean Serenevy, Olivier Houdas, FYI: by these changes you loose the ability to see custom field dates on your calendar...
Currently, I have disabled this feature for other adapters than mysql.
#4
Updated by Andriy Lesyuk over 10 years ago
Olivier Houdas, it would be great, if you could test the solution for MS SQL:
- Create an issue custom field of the Date type.
- Add some value for this custom field for some issue.
- Replace
STR_TO_DATE(#{CustomValue.table_name}.value, '%Y-%m-%d')withCONVERT(datetime, #{CustomValue.table_name}.value, 120). - Restart Redmine.
- Check, if the date is shown on the calendar.
Use your version of the Extended Fields! Do not update to r115 (it disables calendar changes for MS SQL).
#5
Updated by Andriy Lesyuk over 10 years ago
Dean Serenevy, you can help enabling the feature for PostgreSQL! It would be great, actually, if you could:
- Create an issue custom field of the Date type.
- Add some value for this custom field for some issue.
- Replace
STR_TO_DATE(#{CustomValue.table_name}.value, '%Y-%m-%d')withTO_DATE(#{CustomValue.table_name}.value, 'YYYY-MM-DD'). - Restart Redmine.
- Check, if the date is shown on the calendar.
Use your version of the Extended Fields! Do not update to r115 (it disables calendar changes for PostgreSQL).
#6
Updated by Andriy Lesyuk over 10 years ago
- Status changed from In Progress to Incomplete
- Target version deleted (
0.2.3)
#7
Updated by Olivier Houdas over 10 years ago
Updated by Sorry for the late reply, I haven’t received mails with your comments...
I tried what you said, but to no avail:
ActiveRecord::StatementInvalid (TinyTds::Error: 'TO_DATE’ is not a recognized built-in function name.: (...)
Thank you for looking at this issue!
#8
Updated by Seung Mun over 10 years ago
Updated by As mentioned in #note-5, I replaced STR_TO_DATE with TO_DATE, and Redmine Calendar feature is working again.
There were 3 lines with STR_TO_DATE in extended_fields/lib/extended_calendars_controller_patch.rb
#9
Updated by Olivier Houdas over 10 years ago
I’m afraid, TO_DATE() does NOT exist in MS SQL, so you must be using some other DB or have added that function to your SQL server yourself, Seung.
See http://www.connectsql.com/2011/04/sql-server-basics-todate-function-in.html for example.
I’ve tried CONVERT, value, 120). It does not crash anymore, but it still does not work. In the debug log of Redmine, I get:
(...) AND CONVERT(VARCHAR(10), custom_values.value, 120) BETWEEN ''05-26-2014'' AND ''07-06-2014'')' (...)So I tried CONVERT, value, 110), but could not get my task set for June 25th 2014 (MyCustomDate=2014-06-25) displayed in the calendar.
I'm using r115 in which I commented if ActiveRecord::Base.connection.adapter_name =~ %r{mysql}i
in init.rb
#10
Updated by Seung Mun over 10 years ago
Sorry. I forgot to mention that I am using PostgreSQL.
According to http://www.w3schools.com/sql/func_convert.asp, below line should give you mm-dd-yyyy
CONVERT(VARCHAR(10), #{CustomValue.table_name}.value, 110)
style 120 is 'yyyy-mm-dd hh:mi:ss (24h)'