[SOLVED] Addition of more than one value to DateTime format

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Addition of more than one value to DateTime format

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

your second one is in 6 minute form.. you need an extra set of ':00' I'd think..
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Could do nested DATE_ADD()s? Functions are often easier to work with than not.

Mac
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Thanks twigletmac. This worked :

Code: Select all

ADDDATE(
ADDDATE(NOW(),INTERVAL '01:00' HOUR_MINUTE ),
INTERVAL '05:00' HOUR_MINUTE 
)
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Nested functions are definitely (IMHO) easier to read and manipulate :)

Mac
Post Reply