CODEWITHSUNDEEP
mysqlsqldate
Dan Lindqvist
Dan Lindqvist

Reputation: 65

Calculate difference between dates

The title might be a bit misleading, but what I want is:

SELECT * FROM table ORDER BY pid ASC

And in one of the columns I have a DATE(). I want to compare the current date (not time) and return how many days are left till that date. Let's say the date is 2013-04-20 and today's date is 2013-04-16 I don't want to get any data if it's < current date. If it is I want it returned in days.

I've been looking around here and I've found no way to do it, and I can't for the love of me figure it out.

Upvotes: 0

Views: 914

Answers (3)

Travis
Travis

Reputation: 146

If you're looking for the difference between two date you can use the GETDATE function in MS SQL

SELECT DATEDIFF(DD, DateOne, DateTwo) FROM TABLE

This will return the difference in number of days between the two dates.

If you only want rows where the date field is less than or equal to today's date you can use:

SELECT DATEDIFF(DD, DateField, GETDATE())
FROM TableName
WHERE DateField <= GETDATE()

Upvotes: 1

John Conde
John Conde

Reputation: 219934

If you're using MySQL you can use DATEDIFF()

SELECT 
    DATEDIFF(NOW(), date_column) AS days_diff
FROM
    tablename

Upvotes: 1

user330315
user330315

Reputation:

Get the difference between two dates (ANSI SQL)

select the_date_column - current_date as days_left
from the_table
where the_date_column - current_date <= 4;

SQLFiddle: http://sqlfiddle.com/#!12/3148d/1

Upvotes: 0

Related Questions