?
Solved

find age between two dates and insert into new column

Posted on 2014-03-18
3
Medium Priority
?
331 Views
Last Modified: 2014-03-19
I added an existing column to my DB called AgeReg.   Short for Age of Registration.

I have two other columns which is the Date of Entry (DOE) and Date of Birth (DOB)

How do I run a command to go through the entire table and find the Age relative to the the Date of Entry and then insert it into the the AgeReg column
0
Comment
Question by:al4629740
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 39937868
For instance if you want to find the diff in days between the two dates you can run a function like:

select datediff(day, GETDATE(), (GETDATE()+25))


For different "interval" you can get hours, months, seconds, etc - whatever is supported by DATEDIFF SQL function and to run an update you will do something like:

--to see what will be updated run
select AgeReg, datediff(day, DOE,DOB) as AgeReg_AfterUpdate from tablename

--then run the update
update tablename set AgeReg = datediff(day, DOE,DOB)
0
 

Author Comment

by:al4629740
ID: 39937892
Need to flipflop and change first type

select AgeReg, datediff(year, DOB,DOE) as AgeReg_AfterUpdate from tablename
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39937908
Sure, if you want that AgeReg in years just make that data type an INT and then the update should look like:

-to see what will be updated run
select AgeReg, datediff(year, DOB,DOE)  as AgeReg_AfterUpdate from tablename

--then run the update
update tablename set AgeReg = datediff(year, DOB,DOE)
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question