Calculating Date difference using mysql functions

Today i am going to share two functions with you, one is datediff() and another is to_days() functions.

datediff(), accepts two parameters, both parameters can be either date or date_time. while comparing two dates, mysql takes only date parts for comparison, it will omit the time if the parameter is passed as date_time.

For example,

SELECT DATEDIFF(‘2010-03-21 23:59:59′,’2010-03-20’);

the above compare two dates and return 1

SELECT * from tablename where DATEDIFF(enddate,curdate())>1;

the above query compares enddate (is a field in the table, contains date value) and current date and returns rows whose enddate is greater than 1.

to_days(), accepts one parameters, and returns a day number.

For example,

SELECT DATEDIFF(‘2010-03-21’);

the above returns 734217 as day number.

consider the queries,

SELECT DATEDIFF(‘2010-03-21′),DATEDIFF(’10-03-21’);

this query returns 734217 for both. here mysql automatically converts two digit year into four digit year based on the rules designed in mysql server.

SELECT * from tablename where to_days(enddate) > to_days(curdate());

this query returns rows whose enddate is greater than today date

Author: Vinodkumar

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: