Page 1 of 1

Addition of more than one value to DateTime format

Posted: Wed Oct 06, 2004 5:58 am
by anjanesh

Code: Select all

SELECT DATE_FORMAT(
NOW()+
(INTERVAL '01:00' HOUR_MINUTE)+
(INTERVAL '05:00' HOUR_MINUTE),
'%W, %D %M %Y, %r'
)
gives MySQL Error

Code: Select all

SELECT DATE_FORMAT(
NOW()+
(INTERVAL '01:00'+'05:00' HOUR_MINUTE)
'%W, %D %M %Y, %r'
)
adds 6 minutes instead of hours
Any other way out ? Easy MySQL way - not PHP code to add 01:00 to 05:00 to give 06:00.
Thanks

Posted: Wed Oct 06, 2004 6:22 am
by feyd
your second one is in 6 minute form.. you need an extra set of ':00' I'd think..

Posted: Wed Oct 06, 2004 7:05 am
by twigletmac
Don't forget to read the docs - have a look at DATE_ADD() here: http://dev.mysql.com/doc/mysql/en/Date_ ... tions.html

Mac

Posted: Wed Oct 06, 2004 7:24 am
by anjanesh
feyd wrote:your second one is in 6 minute form.. you need an extra set of ':00' I'd think..
I dont think so - 05:00 for attribute HOUR_MINUTE stands for 5 hrs, 0 mins

Code: Select all

SELECT DATE_FORMAT(
NOW()+
INTERVAL '05:00' HOUR_MINUTE,
'%W, %D %M %Y, %r'
) 
adds 5 hrs not mins
Most of the enhanced Time functions were introduced in MySQL 4.1. Ver 4.0 exists in most web servers.

Posted: Wed Oct 06, 2004 7:43 am
by twigletmac
Could do nested DATE_ADD()s? Functions are often easier to work with than not.

Mac

Posted: Wed Oct 06, 2004 11:21 pm
by anjanesh
Thanks twigletmac. This worked :

Code: Select all

ADDDATE(
ADDDATE(NOW(),INTERVAL '01:00' HOUR_MINUTE ),
INTERVAL '05:00' HOUR_MINUTE 
)

Posted: Thu Oct 07, 2004 2:53 am
by twigletmac
Nested functions are definitely (IMHO) easier to read and manipulate :)

Mac