Mysql date difference?-CodePudding

task is to get date difference and here's my sql statement:

select start_date, end_date, a_name
           from b
           WHERE DateDiff(start_date, end_date) AS DF >= 1000 AND
           department_name = 'abc';

And I keep getting errors. I am new to SQL so kinda lost now.

CodePudding user response：

as provides an alias (a name) for a result column or joined table or subquery; it doesn't belong in the middle of an expression.

CodePudding user response：

There are two mistakes in your query. The first one is the syntax error. Alias names are given in the SELECT clause, not in the WHERE clause. What would that alias name be for, anyway? Remove AS DF.

Then, there is a semantical error. You are comparing the two dates with DATEDIFF and thus get the difference in days. DATEDIFF subtracts the second date from the first one. Accordingly, you subtract the end_date from the start_date, which will result in a negative number. See here how DATEDIFF works in MySQL: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_datediff).

Here is the corrected query:

select start_date, end_date, a_name
from b
where datediff(end_date, start_date) >= 1000 
and department_name = 'abc'
order by start_date;

When not sure which way DATEDIFF works and to avoid this doubt with any future reader, you can also use ABS here:

where abs(datediff(end_date, start_date)) >= 1000

Thus the order of the two parameters doesn't matter any more and everybody sees at first glance that this must work.