/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: airwolf 11 years ago
Red Belt
0
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 11 years ago
Orange Senior Belt
0
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 11 years ago
Red Belt
0
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.
Posted by: kaneda0149 11 years ago
Orange Senior Belt
0
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 11 years ago
Red Belt
0
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 11 years ago
Orange Senior Belt
0
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 11 years ago
Red Belt
0
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 11 years ago
Orange Senior Belt
0
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 11 years ago
Red Belt
0
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.
Posted by: kaneda0149 11 years ago
Orange Senior Belt
0
Makes sense, thanks for your help!
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ