CODEWITHSUNDEEP
mysqldatediff
djeetee
djeetee

Reputation: 1837

DATEDIFF in MySQL query

I would like to used the DATEDIFF function like so in a MySQL query: 

SELECT `ItemType`, 
       `DateOpen` AS StartDate, 
       IFNULL (`DateClosed`, CURDATE()) AS EndDate, 
       DATEDIFF(`EndDate`, `StartDate`) AS ItemLife
FROM `Items` 
WHERE `ProjectID`=11
ORDER BY `ItemType` ASC

The above query fails because of the DATEDIFF part. I tried the column names with and without the the back ticks with no difference. Is my syntax wrong or am I violating some some SQL language rule?

Taking the DATEDIFF part out makes the query run smoothly.

hope someone can help.

thanks

Upvotes: 2

Views: 14043

Answers (1)

Imre L
Imre L

Reputation: 6249

You cannot use aliases you have defined in same query's select and where section (but you can in in group by, having,order by and aliases defined in subqueries)

SELECT `ItemType`, 
       `DateOpen` AS StartDate, 
       IFNULL (`DateClosed`, CURDATE()) AS EndDate, 
       DATEDIFF(IFNULL (`DateClosed`, CURDATE()), `DateOpen`) AS ItemLife
FROM `Items` 
WHERE `ProjectID`=11
ORDER BY `ItemType` ASC

or

SELECT t.*,
       DATEDIFF(`EndDate`, `StartDate`) AS ItemLife
FROM (
    SELECT `ItemType`, 
           `DateOpen` AS StartDate, 
           IFNULL (`DateClosed`, CURDATE()) AS EndDate
    FROM `Items` 
    WHERE `ProjectID`=11
    ORDER BY `ItemType` ASC
) t

Upvotes: 8

Related Questions