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

[planetthoughtful]

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

[Thomas Bartkus]

<pl**************@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

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!

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 {datetimefield}.

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

[Bill Karwin]

Thomas Bartkus wrote:

Now you only need to convert seconds to days.

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/...functions.html

Regards,
Bill K.

[planetthoughtful]

Hi Thomas,

Thanks for this - very helpful!

Much warmth,

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