If you store the Age in table 2, will you run a job everyday to keep the age current? I don't think that I would do it that way. I would either add the age calculation to my select when I pull the users data, or actually add a computed field to Table
1 for the age. Then the Computed Column Specification for Age in Table 1 would be:
(datediff(year,[Birthdate],getdate())-case when datepart(month,[Birthdate])>datepart(month,getdate()) OR datepart(month,[Birthdate])=datepart(month,getdate()) AND datepart(day,[Birthdate])>datepart(day,getdate()) then (1) else (0) end)
No, I did follow. I was just pointing out different ways of doing things. Even though you run this code, tomorrow you may have rows with invalid ages, so you would have to run this every day to ensure the ages are correct.
Here is some code that will update the age in Table2. *** TEST THIS BEFORE USING ***
UPDATE Table2
SET Age =
(SELECT DATEDIFF(yy, Birthdate, GETDATE()) -
CASE WHEN
(MONTH(Birthdate) > MONTH(GETDATE()))
OR (MONTH(Birthdate) = MONTH(GETDATE()) AND DAY(Birthdate) > DAY(GETDATE()))
THEN 1
ELSE 0
END
FROM Table1
WHERE Table1.UserId = Table2.UserId)
Even though you run this code, tomorrow you may have rows with invalid ages, so you would have to run this every day to ensure the ages are correct.
What I meant was, if you run your code today Jan 3, 2013, the ages will be based on that date. So, if someone has a birthday of Jan 4, 2005, their age that will be stored in the table will be 7. Tomorrow, the table will still show 7, even though it is their
birthday and they will be 8.
prontonet
Once done I should have the correct age of all the Users and I can bind that column to a data control on my web page as required. The user can update their age if they want manually
So the user will have to correct their age. However, instead of binding the table2 age to the data control on the web page (and wherever you need it), bind the computed age from the SELECT statement and the age will always be correct.
Marked as answer by prontonet on Jan 04, 2013 01:45 PM
prontonet
Member
243 Points
484 Posts
Re: SELECT SQL QUERY USING CURRENT DATE TIMETO GET THE USERS AGE?
Jan 02, 2013 07:09 PM|LINK
Thats great thank you. To fit all this together can you advise on how
to update all the persons Age in the SQL Database using Update/insert statement
In my SQL database I have 2 Tables.
Table1
UserId - ( Primary Key of Type GUID )
Birthdate (DateTime)
Table 2
ProfileId - (Primary Key of Type Int)
Age (of type INT)
UserId (of Type Guid)
I have every Users Birthdate in Table 1 and I want to calcualte the Persons Age today
and update the AGE Column in Table 2 with the correct number for every UserId.
Some of the Age columns are populated already some not - but I want to
automatically update each using your method as some may be wrong.
paindaasp
Star
12092 Points
2035 Posts
Re: SELECT SQL QUERY USING CURRENT DATE TIMETO GET THE USERS AGE?
Jan 03, 2013 11:37 AM|LINK
If you store the Age in table 2, will you run a job everyday to keep the age current? I don't think that I would do it that way. I would either add the age calculation to my select when I pull the users data, or actually add a computed field to Table 1 for the age. Then the Computed Column Specification for Age in Table 1 would be:
prontonet
Member
243 Points
484 Posts
Re: SELECT SQL QUERY USING CURRENT DATE TIMETO GET THE USERS AGE?
Jan 03, 2013 12:35 PM|LINK
hi,
i dont quite fully understand.
the problem is in Table 2, I have AgeId (int) Column which has incorrect values. ( because there was
a DDL on the User Registration Form which was bound to a seperate Age Table in SQL
AgeId is A foreign Key in Table 1
Subsequently I changed Age Column in SQL to extend it and now the ages that users entered are incorrect.
So i want to Update The AgeId Somehow in Table 2 without having to do each one manually
This sound confusing I think. niot sure if you follow?
paindaasp
Star
12092 Points
2035 Posts
Re: SELECT SQL QUERY USING CURRENT DATE TIMETO GET THE USERS AGE?
Jan 03, 2013 01:59 PM|LINK
No, I did follow. I was just pointing out different ways of doing things. Even though you run this code, tomorrow you may have rows with invalid ages, so you would have to run this every day to ensure the ages are correct.
Here is some code that will update the age in Table2. *** TEST THIS BEFORE USING ***
UPDATE Table2 SET Age = (SELECT DATEDIFF(yy, Birthdate, GETDATE()) - CASE WHEN (MONTH(Birthdate) > MONTH(GETDATE())) OR (MONTH(Birthdate) = MONTH(GETDATE()) AND DAY(Birthdate) > DAY(GETDATE())) THEN 1 ELSE 0 END FROM Table1 WHERE Table1.UserId = Table2.UserId)prontonet
Member
243 Points
484 Posts
Re: SELECT SQL QUERY USING CURRENT DATE TIMETO GET THE USERS AGE?
Jan 03, 2013 03:25 PM|LINK
right. great thanks, just to clarify that once more.
I dont want to run code everyday. - I just want to get the correct Age of the User as of TODAY
by running a query against Their Birthdate which is a populated column in Table 1.
The result will then return a Number and that (INT) I want to INSERT into the Table 2 AGE column.
(does the code you advised do that)
Once done I should have the correct age of all the Users and I can bind that column to
a data control on my web page as required.
The user can update their age if they want manually- but lets say they forget to and in a year I run the
code again - that would bring it up to date again right??
many thanks
paindaasp
Star
12092 Points
2035 Posts
Re: SELECT SQL QUERY USING CURRENT DATE TIMETO GET THE USERS AGE?
Jan 03, 2013 03:33 PM|LINK
Yes, the code will do as you stated. As I said before, make sure you test it first.
prontonet
Member
243 Points
484 Posts
Re: SELECT SQL QUERY USING CURRENT DATE TIMETO GET THE USERS AGE?
Jan 03, 2013 09:22 PM|LINK
I am just a little confused by the line below you mentioned earlier - is this still relevant?
Even though you run this code, tomorrow you may have rows with invalid ages, so you would have to run this every day to ensure the ages are correct.
paindaasp
Star
12092 Points
2035 Posts
Re: SELECT SQL QUERY USING CURRENT DATE TIMETO GET THE USERS AGE?
Jan 04, 2013 12:15 AM|LINK
What I meant was, if you run your code today Jan 3, 2013, the ages will be based on that date. So, if someone has a birthday of Jan 4, 2005, their age that will be stored in the table will be 7. Tomorrow, the table will still show 7, even though it is their birthday and they will be 8.
So the user will have to correct their age. However, instead of binding the table2 age to the data control on the web page (and wherever you need it), bind the computed age from the SELECT statement and the age will always be correct.
prontonet
Member
243 Points
484 Posts
Re: SELECT SQL QUERY USING CURRENT DATE TIMETO GET THE USERS AGE?
Jan 04, 2013 01:45 PM|LINK
tested ok and updated ok in SQL. thanks a million for your help on this.