Using the Datetime type is quite hard if you haven't used the various methods that comes with it. You can read about them all here, and in this article which will show some simple examples for all of them.

Let's take the following table:

id	entry	date
1	I am a happy camper	2009-11-01 09:35:35
2	I am a capped hampster	2009-11-02 09:35:49
3	I am a wrapped tramp	2009-11-03 09:36:10
4	I am a crappy camper	2009-11-04 09:36:27
5	I am a lappy santa	2009-11-05 09:36:43
6	I am a snappy tamper	2009-11-06 09:37:03

Year(), Day() and Month()

Now, what we want to do is to select all entries ranging from the 1st of november to the 3rd of november. To do this, we could do something clumpsy like:



But a much better way to do this is to this is to use the datetime functions at hand:



This will give the same effect, but is alot easier to read and handle. The Year() and Month() functions works the same way.

The above will yield:

entry	date
I am a happy camper	2009-11-01 09:35:35
I am a capped hampster	2009-11-02 09:35:49
I am a wrapped tramp	2009-11-03 09:36:10

DATE_ADD

If you want to add an interval to a date, use the DATE_ADD function like this:



This will yield:

entry	date
I am a happy camper	2009-11-11 09:35:35

Other useful intervals are:

• SECOND
• MINUTE
• HOUR
• DAY
• MONTH
• YEAR

There are more, like microseconds etc, but these are the basic ones.

DATE_SUB

If you want to subtract an interval from a date, use the DATE_SUB function, like this:



This will yield:

entry	date
I am a happy camper	2009-10-22 09:35:35

DATEDIFF

With the DATEDIFF() function you can get the difference between two dates, like this:



This will yield:

difference	date	entry
31	2009-10-22 09:35:35	I am a happy camper