Solved

MySQL GET_FORMAT() not working as expecte

Posted on 2010-08-13
2
388 Views
Last Modified: 2013-12-13
Hello,

I'm trying to update one datetime field based on other datetime filed that is bigger than 1 day than the other

This is my query:

UPDATE TABLE SET `field`='amyvalue' WHERE datetime_field < GET_FORMAT(UNIX_TIMESTAMP(`other_datetime_field`)-86400, 'ISO')


I'm getting this error:
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNIX_TIMESTAMP(`other_datetime_field`)-86400, 'ISO')' at line 1


How can I use GET_FORMAT() to get it to work properly ?

Thank you
0
Comment
Question by:Ionut A. Tudor
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
what is the data type of datetime_field and other_datetime_field, please?if it's really datetime:http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.htmlhttp://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add
UPDATE TABLE 

  SET `field`='amyvalue' 

WHERE datetime_field < date_sub(`other_datetime_field`, INTERVAL 1 DAY)

Open in new window

0
 
LVL 14

Author Comment

by:Ionut A. Tudor
Comment Utility
Yes angelll, I needed it for doing the below and it worked. Thanks

UPDATE orders SET `other_datetime_field`=DATE_SUB(`datetime_field`, INTERVAL 2 DAY) WHERE id='1' LIMIT 1

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now