Search Marketing

   
   

Comparing Dates and Selecting Date Differences

Fri May 02, 2008 9:54 pm
<<     >>
Comments: 0 Views: 4066

There are many reason you might use the DATE_DIFF function in MySQL. You could simply compare the length of days between two dates or check if a date is in the future or the past.

Using the following table.

mysql> select * from Name;
+----+--------+---------------------+
| ID | Name | Date |
+----+--------+---------------------+
| 1 | Joe | 2001-02-12 00:00:00 |
| 2 | Jack | 2003-03-15 00:00:00 |
| 3 | John | 1980-05-15 00:00:00 |
| 4 | James | 1994-09-21 00:00:00 |
| 5 | George | 1996-12-11 00:00:00 |
| 6 | Paul | 2007-10-09 12:54:58 |
+----+--------+---------------------+

Lets see, in days how distant any of the above dates are from the current date.

mysql> select Date,DateDiff(NOW(),Date) from Name;
+---------------------+----------------------+
| Date | DateDiff(NOW(),Date) |
+---------------------+----------------------+
| 2001-02-12 00:00:00 | 2432 |
| 2003-03-15 00:00:00 | 1671 |
| 1980-05-15 00:00:00 | 10010 |
| 1994-09-21 00:00:00 | 4768 |
| 1996-12-11 00:00:00 | 3956 |
| 2007-10-09 12:54:58 | 2 |
+---------------------+----------------------+

We can see that the result of DateDiff provides the number of days in the past these dates are.

If we wanted to compare the year 2000 to these dates, you would use the following command.

mysql> select Date,DateDiff(Date,'2000-01-01') from Name;
+---------------------+-----------------------------+
| Date | DateDiff(Date,'2000-01-01') |
+---------------------+-----------------------------+
| 2001-02-12 00:00:00 | 408 |
| 2003-03-15 00:00:00 | 1169 |
| 1980-05-15 00:00:00 | -7170 |
| 1994-09-21 00:00:00 | -1928 |
| 1996-12-11 00:00:00 | -1116 |
| 2007-10-09 12:54:58 | 2838 |
+---------------------+-----------------------------+

You can see the result has produced positive and negative numbers. The positive numbers represent the number of days in the future, from the date January 1st 2000, and the negative numbers are the number of days in the past before the date January 1st 2000.

If you have a series of dates and wanted to determine which dates were in the future from this moment then you would use the following command.

mysql> select Date,DateDiff(Date,NOW()) from Name;
+---------------------+----------------------+
| Date | DateDiff(Date,NOW()) |
+---------------------+----------------------+
| 2001-02-12 00:00:00 | -2432 |
| 2003-03-15 00:00:00 | -1671 |
| 1980-05-15 00:00:00 | -10010 |
| 1994-09-21 00:00:00 | -4768 |
| 1996-12-11 00:00:00 | -3956 |
| 2007-10-09 12:54:58 | -2 |
| 2010-01-01 00:00:00 | 813 |
| 2009-12-15 00:00:00 | 796 |
+---------------------+----------------------+

You can see that the positive numbers represent future dates.



Comments
Name:
*
URL: http: (ex. cnn.com)
Comment:
*
Number:
Math (14 + 3)
* required
SubCategories
Books

© 2017 Christonium LLC

Christonium.com
|
Terms of Use
|
Privacy
ccc