DateDiff Between Two Dates
I created a report through the wizard and when I went in to edit the SQL to include a simple datediff in the select statement to get the difference between the open date and closed date. It's giving me a syntax error. Am I missing something? All the help would be appreciated. Thanks!
SELECT HD_CATEGORY.NAME AS CATEGORY, HD_TICKET.CREATED AS HD_TICKET_CREATED,
S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.TIME_CLOSED AS HD_TICKET_TIME_CLOSED,
HD_TICKET.TIME_OPENED AS HD_TICKET_TIME_OPENED, HD_TICKET.CUSTOM_FIELD_VALUE0 AS HD_TICKET_CUSTOM_FIELD_VALUE0,
DATEDIFF(day, HD_TICKET.TIME_OPENED, HD_TICKET.TIME_CLOSED) AS DURATION
FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND ( HD_CATEGORY.NAME LIKE '%Trade Pending%'
AND (DATE(HD_TICKET.TIME_OPENED)> DATE_SUB(NOW(), INTERVAL 7 day)
AND DATE(HD_TICKET.TIME_OPENED)<= NOW())) ORDER BY HD_TICKET.CREATED asc
0 Comments
[ + ] Show comments
Answers (10)
Please log in to answer
Posted by:
airwolf
14 years ago
DATEDIFF only has two input variables. Get rid of that 'day' you've got at the beginning of the function. DATEDIFF shows you the difference in days by default.
SELECT HD_CATEGORY.NAME AS CATEGORY, HD_TICKET.CREATED AS HD_TICKET_CREATED,
S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.TIME_CLOSED AS HD_TICKET_TIME_CLOSED,
HD_TICKET.TIME_OPENED AS HD_TICKET_TIME_OPENED, HD_TICKET.CUSTOM_FIELD_VALUE0 AS HD_TICKET_CUSTOM_FIELD_VALUE0,
DATEDIFF(HD_TICKET.TIME_OPENED, HD_TICKET.TIME_CLOSED) AS DURATION
FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND ( HD_CATEGORY.NAME LIKE '%Trade Pending%'
AND (DATE(HD_TICKET.TIME_OPENED)> DATE_SUB(NOW(), INTERVAL 7 day)
AND DATE(HD_TICKET.TIME_OPENED)<= NOW())) ORDER BY HD_TICKET.CREATED asc
Posted by:
kaneda0149
14 years ago
Thanks for the quick response airwolf. How do I get my duration in H:MM:SS format? When it ran, it return 0 value. Is that the day?
Posted by:
airwolf
14 years ago
Well, what do you want that field to show? Using DATEDIFF with TIME_OPENED and TIME_CLOSED, you'll only get a value (non-NULL) if both of those times exist. So, only closed tickets will show you a valid DATEDIFF. If you only want to show opened or closed tickets, you'll need to add the status to your query either way.
At this point, I need to know what you want your report to show you.
At this point, I need to know what you want your report to show you.
Posted by:
kaneda0149
14 years ago
Want the new field to show the the hour, minute and seconds from the time the Ticket was Opened to the time the Ticket was Closed.
Posted by:
airwolf
14 years ago
Then, by definition, you only want to see closed tickets. Also, you want TIMEDIFF, not DATEDIFF. Try this:
SELECT HD_CATEGORY.NAME AS CATEGORY, HD_TICKET.CREATED AS HD_TICKET_CREATED,
S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.TIME_CLOSED AS HD_TICKET_TIME_CLOSED,
HD_TICKET.TIME_OPENED AS HD_TICKET_TIME_OPENED, HD_TICKET.CUSTOM_FIELD_VALUE0 AS HD_TICKET_CUSTOM_FIELD_VALUE0,
TIMEDIFF(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED) AS DURATION
FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE (HD_STATUS.STATE like 'CLOSED') AND (HD_TICKET.HD_QUEUE_ID = 3) AND ( HD_CATEGORY.NAME LIKE '%Trade Pending%'
AND (DATE(HD_TICKET.TIME_OPENED)> DATE_SUB(NOW(), INTERVAL 7 day)
AND DATE(HD_TICKET.TIME_OPENED)<= NOW())) ORDER BY HD_TICKET.CREATED asc
Posted by:
kaneda0149
14 years ago
Airwolf, that was SaWeeet! One last item, can it show without the actual date? Also why is the SQL statement in this different from standard SQL?
Posted by:
airwolf
14 years ago
can it show without the actual date?
I'm not sure what you mean...
Also why is the SQL statement in this different from standard SQL?
I don't know what you mean by "standard SQL". The KBOX uses MySQL, so the syntax is going to be different than something like SQLite, MS SQL, or Oracle.
Posted by:
kaneda0149
14 years ago
Sorry new Duration value returned was in the format of "01/01/1970 00:08:25", just want to see "00:08:25". Sorry I'm used to using MS SQL and it threw me off, did not realize it was using MySQL. Thanks for explanation ;o)
Posted by:
airwolf
14 years ago
Duration should already return as HH:MM:SS. TIMEDIFF returns a result in HH:MM:SS. You can try wrapping the TIMEDIFF function with TIME(), but that is redundant. Your CREATED, CLOSED, and OPENED times will all be shown in YYYY-MM-DD HH:MM:SS format.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.