DATEDIFF SQL Query

Question

I am at the final stage of my project and have the problem to find if a job is overdue. I link this to priority for example if a job has a priority of 1 it must be complete in 1 day, a priority of 4 then 4 days.

I have come up with a CASE however this doesn't seem to work any help would be appreciated.

SELECT `defect_Id`,`Overtasked`
WHERE
    CASE DATEDIFF(DD,`date_Investigaton` - `CURRENT_DATE()`) >= `priority` AS Overtasked
    THEN `Overtasked` == 'YES'
    ELSE `Overtasked` == 'NO'
END

Solution

  `SELECT defect_Id, 
   CASE WHEN DATEDIFF(date_Investigated, CURDATE()) >= priority 
   THEN 'YES' 
   ELSE 'NO' 
   END AS Overtasked 
   FROM defect_report 
   WHERE defect_Id = '82'`

Appreciate the guidance you guys give!

Can you add some sample table data, and the expected result? — jarlh, Apr 07 '16 at 09:16
`DATEDIFF(DD, date_Investigaton - CURRENT_DATE())` -- what is `DD`? — axiac, Apr 07 '16 at 09:22
DD is the abbreviation for day, but the syntax is messed up. It should be this: DATEDIFF(DD, date_Investigaton, getdate()) — Gabor, Apr 07 '16 at 09:26
[`DATEDIFF()`](http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_datediff) expects only 2 arguments on `MySQL` — axiac, Apr 07 '16 at 09:27
dd refers to day as I am intending to only use the day to work out whether the difference is greater than the priority. — Max Tyson, Apr 07 '16 at 09:28
@MaxTyson You posted a link to the `SQL Server`s version of `DATEDIFF()` but tagged the question with `MySQL`. They are different products that have different syntax rules for queries and different functions. Which one do you use? — axiac, Apr 07 '16 at 09:30
Forget about w3school. Use the official MySQL documentation. This is how the function `DATEDIFF()` works: http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_datediff — axiac, Apr 07 '16 at 09:35
@MaxTyson You [can answer](http://stackoverflow.com/help/self-answer) to your own question, you know? :-) — axiac, Apr 07 '16 at 09:46

score 0 · Answer 1 · answered Apr 07 '16 at 09:24

0

You are completely mixing up SQL dialects and even there are syntax errors. Assuming you are talking about MS SQL Server let's try this:

SELECT  defect_Id, 
        CASE WHEN DATEDIFF(DD, date_Investigaton, getdate()) >= priority
            THEN  'YES'
            ELSE  'NO'
        END AS Overtasked
FROM    <YourTable>
WHERE   <YourWhereIfAny>

answered Apr 07 '16 at 09:24

Gabor

1,409
1
14
24

I assume the back-ticks implies MySQL. – jarlh Apr 07 '16 at 09:26

score 0 · Answer 2 · edited May 23 '17 at 12:07

If date_Investigation is a DATE column, the subtraction date_Investigation - CURRENT_DATE() produces the number of days you need.

Otherwise (if it is a DATETIME, for example) both operands are converted to float and the result is something you are totally not expecting. For such situations use the DATEDIFF() function. It interprets its arguments as DATE (ignores the time part) and returns the integer number of days between the two dates.

Your query should be like:

SELECT
    `defect_Id`,
    IF (DATEDIFF(`date_Investigaton`, CURRENT_DATE()) >= `priority`, 'YES', 'NO')
        AS `Overtasked`
FROM [...your table name here...]
WHERE [...conditions...]

Replace the parts in square brackets ([...]) with the name of the table where to get the data from and some conditions to limit the number of returned rows (otherwise it will get the entire table which, most probably, is not what you want).

Btw, CURRENT_DATE() is also a function. If you write it in backquotes (``), MySQL will try to find a column with this name and it will fail.

Read the accepted answer for this question. It explains when to use back ticks, single quotes or double quotes in MySQL (and partially in PHP).

DATEDIFF SQL Query

2 Answers2