PHP MYSQL update a DATE and TIME field in db manually

Question

I am trying to figure out how to update a MYSQL DATE and TIME field manually (NOT to todays date!)ie set it the date field to a certain date value and time field to a certain time field correctly in the correct SQL required field format.

I am using an UPDATE query not INSERT as my action is to update a users field

Done some research and came up with something along the lines of (obviously this example wont work but does anyone know how to format this query?

UPDATE mytblname SET date='DATE: Manual Date', '2011-06-14'', time='TIME: Manual Time', '12:10:00' WHERE email='somevalue'

If I just enter the value as normal SQL way it gives 0000-00-00 for date and 00:00:00 for time - ie

SET date='2011-06-14',time='12:33:35'

Thanks for any suggestions, really appreciate it!!!

Brad · Accepted Answer · 2011-07-07 20:54:20Z

13

UPDATE mytblname SET `date`="2011-06-14", `time`="12:10:00" WHERE email="somevalue";

This should work fine. Make sure you have the appropriate backticks around date and time.

answered Jul 7, 2011 at 20:54

Brad

164k57 gold badges380 silver badges559 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

daza166 Over a year ago

No doing it directly that way caused the problem

Brad Over a year ago

@daza166, What problem? It does work, trust me. If it isn't working for you, then chances are your WHERE condition isn't matching what you want. Have you checked case sensitivity of that email field, for example?

daza166 Over a year ago

I found the problem, that way does work I was putting the time into the date field and the date into the time field lol silly mistake. Thanks

Tadeck · Accepted Answer · 2011-07-07 21:07:09Z

3

Please refer to the MySQL Documentation on DATE, TIME and DATETIME formats. You can see there, that there are multiple possibilities of the values that can be assigned to fields of these types.

So this should work:

UPDATE `mytblname` SET `date`=NOW(), `time`=NOW() WHERE `email`='somevalue';

or to any specific date like that (the string will be automatically converted to DATE, TIME or DATETIME format):

UPDATE `mytblname`
SET
`date`='1987-01-02 11:22:33',
`time`='1987-01-02 11:22:33'
WHERE `email`='somevalue';

You can also assign it like that, which is more clear:

UPDATE `mytblname`
SET
`date`='1987-01-02',
`time`='11:22:33'
WHERE `email`='somevalue';

The only question is, which path will you choose :)

edited Jul 7, 2011 at 21:07

answered Jul 7, 2011 at 21:00

Tadeck

138k28 gold badges155 silver badges201 bronze badges

3 Comments

daza166 Over a year ago

The thing is I dont want to set the date and time to todays I want to set it to a manual date other than todays?

Tadeck Over a year ago

@saza166 Maybe you have DATETIME fields and this is the reason why some parts of it are zeros?

daza166 Over a year ago

I found the problem, that way does work I was putting the time into the date field and the date into the time field lol silly mistake. Thanks for detailed answer but I just can put it in like date='2011-06-08' and time='06:11:23' etc

Collectives™ on Stack Overflow

PHP MYSQL update a DATE and TIME field in db manually

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related