I have a report i'm generating and i'm including the time the ticket was created in the report as well as time opened and time closed.  I was wondering if there is a way to have it show only the date and not the hours, minutes, and seconds along with it.

Answer Summary:
SELECT HD_TICKET.CUSTOM_FIELD_VALUE4, HD_TICKET.CUSTOM_FIELD_VALUE7, HD_TICKET.ID, HD_STATUS.NAME AS STATUS, DATE_FORMAT(HD_TICKET.CREATED, '%w %M %Y'), DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%w %M %Y'), DATE_FORMAT(HD_TICKET.TIME_OPENED, '%w %M %Y') FROM HD_TICKET JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) ORDER BY CUSTOM_FIELD_VALUE4 , CUSTOM_FIELD_VALUE7 , STATUS This results in '', '', '4', 'Closed', '2 October 2012', '3 December 2012', '2 December 2012'
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

Here you go,

You can check the link to change it to different formats,

 

SELECT 

    HD_TICKET.CUSTOM_FIELD_VALUE4,

    HD_TICKET.CUSTOM_FIELD_VALUE7,

    HD_TICKET.ID,

    HD_STATUS.NAME AS STATUS,

    DATE_FORMAT(HD_TICKET.CREATED, '%w %M %Y'),

    DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%w %M %Y'),

    DATE_FORMAT(HD_TICKET.TIME_OPENED, '%w %M %Y')

FROM

    HD_TICKET

        JOIN

    HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)

WHERE

    (HD_TICKET.HD_QUEUE_ID = 1)

ORDER BY CUSTOM_FIELD_VALUE4 , CUSTOM_FIELD_VALUE7 , STATUS

 

This results in 

 

'', '', '4', 'Closed', '2 October 2012', '3 December 2012', '2 December 2012'

Answered 02/08/2013 by: jdornan
Red Belt

  • That worked thank you very much. Gives me a bit of understanding seeing the whole thing typed out this way instead of the way it is in kace. One thing though, now that i've used this, my headings in the report for the time created and time opened and time closed now look like this
    DATE FORMAT(HD TICKET.CREATED, '%m.%e.%y')
    DATE FORMAT(HD TICKET.TIME CLOSED, '%m.%e.%y')
    DATE FORMAT(HD TICKET.TIME OPENED, '%m.%e.%y')
    before they just said time created, time opened, and time closed.
  • Glad to help
  • so is there a way to fix the headings on the report to say just the heading name and not the sql stuff?
  • SELECT

    HD_TICKET.CUSTOM_FIELD_VALUE4,

    HD_TICKET.CUSTOM_FIELD_VALUE7,

    HD_TICKET.ID,

    HD_STATUS.NAME AS STATUS,

    DATE_FORMAT(HD_TICKET.CREATED, '%w %M %Y') AS CREATED,

    DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%w %M %Y') AS CLOSED,

    DATE_FORMAT(HD_TICKET.TIME_OPENED, '%w %M %Y') AS OPENED

    FROM

    HD_TICKET

    JOIN

    HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)

    WHERE

    (HD_TICKET.HD_QUEUE_ID = 1)

    ORDER BY CUSTOM_FIELD_VALUE4 , CUSTOM_FIELD_VALUE7 , STATUS
  • Your Awsome. That worked perfect. Thanks so much for the help. This has helped me to understand the syntax of how some of this sql stuff works and should help me alot in the long run. Again thank you for your time and expertise.
Please log in to comment

Answers

0

Try DateFormat()

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Answered 02/08/2013 by: jdornan
Red Belt

  • Here is my sql string for the report. I'm not sure where to put that dateformat command in. and do i havve to put it in for all 3 time sections? I have time created, Time Opened, and Time closed, and all 3 of them show the full timestamp. do i use this command in all 3 places or just once? Sorry I'm not very literate in sql.
  • SELECT HD_TICKET.CUSTOM_FIELD_VALUE4, HD_TICKET.CUSTOM_FIELD_VALUE7, HD_TICKET.ID, HD_STATUS.NAME AS STATUS, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED FROM HD_TICKET JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 4) ORDER BY CUSTOM_FIELD_VALUE4, CUSTOM_FIELD_VALUE7, STATUS
Please log in to comment
Answer this question or Comment on this question for clarity