| View previous topic :: View next topic |
| Author |
Message |
baron
Joined: Fri May 13, 2005 6:54 pm
Posts: 75
Location: Cheshire
|
 Posted: Fri Jan 06, 2006 1:28 pm Post subject: Manipulating dates in mysql |
 |
|
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 |
|
| Back to top |
|
 |
firefox
Joined: Mon Apr 11, 2005 12:21 pm
Posts: 64
|
| Posted: Fri Jan 06, 2006 2:53 pm Post subject: |
|
|
Use UPDATE instead:
| Code: |
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. |
|
| Back to top |
|
|
nelz
Moderator
Joined: Mon Apr 04, 2005 12:52 pm
Posts: 8036
Location: Warrington, UK
|
| Posted: Fri Jan 06, 2006 3:52 pm Post subject: |
|
|
You have one more ( than ).
_________________
"Insanity: doing the same thing over and over again and expecting different results." (Albert Einstein) |
|
| Back to top |
|
|
| View previous topic :: View next topic |
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
