Manipulating dates in mysql

Code junkies hangout here

Moderators: ChrisThornett, LXF moderators

Manipulating dates in mysql

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

Hi

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?

Baron
baron
 
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
UPDATE Dates
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.
firefox
 
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
nelz
Site admin
 
Posts: 8527
Joined: Mon Apr 04, 2005 11:52 am
Location: Warrington, UK


Return to Programming

Who is online

Users browsing this forum: No registered users and 1 guest