Linux Format forums Forum Index Linux Format forums
Help, discussion, magazine feedback and more
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Manipulating dates in mysql

 
Post new topic   Reply to topic    Linux Format forums Forum Index -> Programming
View previous topic :: View next topic  
Author Message
baron



Joined: Fri May 13, 2005 6:54 pm
Posts: 77
Location: Cheshire

PostPosted: Fri Jan 06, 2006 1:28 pm    Post subject: Manipulating dates in mysql Reply with quote

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
View user's profile Send private message
firefox



Joined: Mon Apr 11, 2005 12:21 pm
Posts: 64

PostPosted: Fri Jan 06, 2006 2:53 pm    Post subject: Reply with quote

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
View user's profile Send private message
nelz
Site admin


Joined: Mon Apr 04, 2005 12:52 pm
Posts: 8369
Location: Warrington, UK

PostPosted: Fri Jan 06, 2006 3:52 pm    Post subject: Reply with quote

You have one more ( than ).
_________________
"Insanity: doing the same thing over and over again and expecting different results." (Albert Einstein)
Back to top
View user's profile Send private message
View previous topic :: View next topic  
Display posts from previous:   
Post new topic   Reply to topic    Linux Format forums Forum Index -> Programming All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Linux Format forums topic RSS feed 


Powered by phpBB © 2001, 2005 phpBB Group


Copyright 2011 Future Publishing, all rights reserved.


Web hosting by UKFast