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

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?

wrote in message
news:1147160202.537323.246940@u72g2000cwu.googlegroups.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

Re: Determining number of days between today and value in datetimefield without DATEDIFF?

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/en/date-and-time-functio ns.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