How to change timestamp in Kace helpdesk reports
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.
Answers (2)
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'
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'
Comments:
-
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. - Crispyominus 11 years ago -
Glad to help - jdornan 11 years ago
-
so is there a way to fix the headings on the report to say just the heading name and not the sql stuff? - Crispyominus 11 years ago
-
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 - jdornan 11 years ago -
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. - Crispyominus 11 years ago
Try DateFormat()
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
Comments:
-
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. - Crispyominus 11 years ago
-
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 - Crispyominus 11 years ago
