I'm looking for a part-time remote job.

Hire me


I'm the author of:

Mastering Redmine is a comprehensive guide with tips, tricks and best practices, and an easy-to-learn structure.

Check the book's project or

Buy the book

Social pages of the book:

By buying this book you also donate to Redmine (see this page).


Follow me:

Bug #2242

Message "invalid statement: STR_TO_DATE" when using MS SQL

Added by Olivier Houdas about 6 years ago. Updated over 5 years ago.

Status:
Incomplete
Priority:
Normal
Assignee:
Category:
-
Target version:
-
Start date:
04 Oct 2013
Due date:
% Done:

30%

Redmine version:
2.3.x
External issue:

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

Revision 115 (diff)
Added by Andriy Lesyuk over 5 years ago

Disabled calendar changes for non-MySQL installations (#2242)

History

#1 Updated by Dean Serenevy about 6 years ago

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}.value

in three places.

#2 Updated by Andriy Lesyuk over 5 years ago

  • Status changed from New to Open
  • Target version set to 0.2.3

#3 Updated by Andriy Lesyuk over 5 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 5 years ago

Olivier Houdas, it would be great, if you could test the solution for MS SQL:

  1. Create an issue custom field of the Date type.
  2. Add some value for this custom field for some issue.
  3. Replace STR_TO_DATE(#{CustomValue.table_name}.value, '%Y-%m-%d') with CONVERT(datetime, #{CustomValue.table_name}.value, 120).
  4. Restart Redmine.
  5. 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 5 years ago

Dean Serenevy, you can help enabling the feature for PostgreSQL! It would be great, actually, if you could:

  1. Create an issue custom field of the Date type.
  2. Add some value for this custom field for some issue.
  3. Replace STR_TO_DATE(#{CustomValue.table_name}.value, '%Y-%m-%d') with TO_DATE(#{CustomValue.table_name}.value, 'YYYY-MM-DD').
  4. Restart Redmine.
  5. 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 5 years ago

  • Status changed from In Progress to Incomplete
  • Target version deleted (0.2.3)

#7 Updated by Olivier Houdas over 5 years ago

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 5 years ago

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 5 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 5 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)'

Also available in: Atom PDF

Terms of use | Privacy policy