# Determining number of days between today and value in datetime field without DATEDIFF?

#### Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

•  Subject
• Author
• Posted on
Hi All,

I'm trying to determine the number of days between today and the value
in a datetime field in a table.
It appears my version of MySQL doesn't have DATEDIFF available, so I'm
wondering if there's another method to achieve the same result as:

SELECT DATEDIFF(CURDATE(),`mydate`) AS numdays FROM tblArticles WHERE
artid = 10

Any help appreciated!

Much warmth,

planetthoughtful
---
"Lost in thought"
http://www.planetthoughtful.org

## Re: Determining number of days between today and value in datetime field without DATEDIFF?

Is the UNIX_TIMESTAMP() function available?
Try "SELECT UNIX_TIMESTAMP(NOW())" and see.

UNIX_TIMESTAMP() returns the number of seconds between the date/time
argument and some base time.   The base time will be different Unix/Linux or
Windows but you really don't care what it is.  Because when you subtract
UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(datetimefield)
you will always get the number of seconds that has elapsed between this
moment and your .

Now you only need to convert seconds to days.

SELECT
(UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(datetimefield)) / (60 * 60 * 24) As
numdays

Returns the value as a floating point value for numdays.
If you need to see it as an integer, enclose it all inside the TRUNCATE() or
ROUND() functions depending upon how you like your integers ;-)

Thomas Bartkus

## Re: Determining number of days between today and value in datetime field without DATEDIFF?

Thomas Bartkus wrote:

FWIW, there's also a TO_DAYS() function in MySQL, so you don't even have
to do the conversion of seconds to days.

See http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

Regards,
Bill K.

## Re: Determining number of days between today and value in datetime field without DATEDIFF?

Hi Thomas,

Thanks for this - very helpful!

Much warmth,

planetthoughtful
---
"lost in thought"
http://www.planetthoughtful.org1