Manipulating dates in mysql

Code junkies hangout here

Moderators: ChriThor, LXF moderators

Manipulating dates in mysql

Postby baron » Fri Jan 06, 2006 1:28 pm


In mysql I have a table called DATES which is part of a seven table database.
The columns of DATES are id, Datejoined, Birthdate, Age and Duration. Information has been entered into the first three columns and I would like to be able to enter the information into the last two columns by calculation using the DATE_SUB and CURDATE() functions. Basically its a calculation of the current date minus birthdate to get Age and a similar calculation using Datejoined to get the Duration.

I wrote the following sql query: INSERT into Dates (Age) Values (DATE_SUB(CURDATE(), INTERVAL Birthdate) where id = 1.

This gave me an error near ') where id = 1. I have tried all sorst of combinations to try to get it to work without sucess.

Can anyone help?

Posts: 78
Joined: Fri May 13, 2005 5:54 pm
Location: Cheshire

Postby firefox » Fri Jan 06, 2006 2:53 pm

Use UPDATE instead:

Code: Select all
SET Age=((DATEDIFF(CURDATE(), Birthdate))/365
WHERE id=1

However, unless this is a field that you will be using often, it goes against database design principles to store this field in the database, because you can calculate the value on-the-fly when you need it.
Posts: 64
Joined: Mon Apr 11, 2005 11:21 am

Postby nelz » Fri Jan 06, 2006 3:52 pm

You have one more ( than ).
"Insanity: doing the same thing over and over again and expecting different results." (Albert Einstein)
User avatar
Site admin
Posts: 9001
Joined: Mon Apr 04, 2005 11:52 am
Location: Warrington, UK

Return to Programming

Who is online

Users browsing this forum: Majestic-12 [Bot] and 0 guests