0

We have SQL query for reports:

SELECT p.value AS __color__,owner AS __group__,id AS ticket, severity,
    priority, status, summary, component, milestone, t.type AS type, time AS created,
    changetime AS _changetime, description AS _description, reporter AS _reporter
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE status = 'assigned' OR status = 'new'
ORDER BY owner, p.value, t.type, time

We need to add one more column to this : duedate in mm/dd/yy format

duedate is a field coming from Custom Field plugin

How would you modify the query to support this?

rkg
  • 5,559
  • 8
  • 37
  • 50
sam
  • 3
  • 1
  • database columns are always **stored** in yyyy/mm/dd, you can **display** them in any format. – Johan Sep 03 '11 at 04:29

1 Answers1

0

First, to retrieve your duedate values you'll need another JOIN, but this time to ticket_custom Trac db table, where all custom ticket field values reside. But this has been already explained in detail in another question here.

Second, custom field values can't be re-formatted on-the-fly, what is especially true for date/time fields. Again more details are available in the SO question linked above. You'll have to live with your input, adopt code form the Custom Time Field proposal or hack on your own (not recommended at all).

Community
  • 1
  • 1
hasienda
  • 2,390
  • 1
  • 13
  • 16
  • Thank you Hasienda! Currently I have edited the report description to indicate which column is in mm/dd/yy and which one in dd/mm/yy so confusion is less – sam Sep 03 '11 at 23:06
  • I could provide code to convert the strings within the SQL statement, if you would confirm, that the 'dd/mm/yy' format is consistent for that field in all tickets. And how are days/months < 9 stored, with or without leading zero? Even rewrite to 'mm/dd/yyyy' would be possible then, if you care. – hasienda Sep 07 '11 at 19:50