Datediff - establish age between two columns

Make it  a computed column

http://technet.microsoft.com/en-us/library/ms188300.aspx

dale has it right

I just need to do this once for a specific set of records.  I don't want to make it a computed column

Well just add the SQL you've shown to the query to wish to use it on?

You seem to already know what to do, or else I'm misunderstanding the question.

Assumptions
   DOR = date of registration
  [BirthDate] and [DOR] are of date/smalldatetime/datetime "data types"

Using a simple datediff(year,BIRTHDATE ,DOR) will not produce a reliable age at registration I'm afraid. Look at these results:

    | USERID |  BIRTHDATE |        DOR | AGEREG | UNRELIABLE_AGEREG |
    |--------|------------|------------|--------|-------------------|
    |    abc | 1975-01-17 | 2013-10-02 |     38 |                38 |
    |    def | 1975-12-31 | 2013-10-02 |     37 |                38 |
    |    ghd | 1996-02-29 | 2013-10-02 |     17 |                17 |
    |    ijk | 1996-02-29 | 2013-01-02 |     16 |                17 |

Select allOpen in new window

The issue is that if the DOR day/month is earlier than the BIRTHDATE's day/month then the age will be incorrect by one year.

The above results were produced by this query:

    SELECT
            UserID
          , convert(varchar(10) ,BirthDate, 121) AS BirthDate
          , convert(varchar(10) ,DOR, 121)       AS DOR
          , YEAR(DOR) - YEAR(BirthDate)
            - ( CASE
                   WHEN MONTH(BirthDate) > MONTH(DOR)
                     OR ( MONTH(BirthDate) = MONTH(DOR)
                        AND DAY(BirthDate) > DAY(DOR)
                        )
                   THEN 1
                   ELSE 0
                 END ) AS AgeReg
    
          , DATEDIFF(YEAR, BirthDate, DOR)  AS unreliable_AgeReg
    FROM Users
;

-- sample data used:
    CREATE TABLE Users
    	( id int identity primary key, [UserID] varchar(12), [BirthDate] datetime, [DOR] datetime)
    ;
    	
    INSERT INTO Users
    	([UserID], [BirthDate], [DOR])
    VALUES
    	('abc', '1975-01-17 00:00:00', '2013-10-02 00:00:00'),
    	('def', '1975-12-31 00:00:00', '2013-10-02 00:00:00'),
    	('ghd', '1996-02-29 00:00:00', '2013-10-02 00:00:00'),
    	('ijk', '1996-02-29 00:00:00', '2013-01-02 00:00:00')
    ;
-- http://sqlfiddle.com/#!3/41e4b/1

Select allOpen in new window

Regarding the computed column, it would make sense if you can implement it as that calculation could be done once (as both dates don't change except for a mistake). However as you can see above it can be simply included into a query like any other calculation would be.

nb: IF (and I hope it's not true) either of the fields used in the calculation are a string type then the calculation would need amendment.

For a little history behind this question, see the question the author posed a couple of days ago:
add computed column from birthdate and time of registration column

What is strange is that Scott provided a valid answer, it was accepted and yet now the author wants to use the following which we all know is not the way to calculate age:
DATEDIFF(YEAR, BirthDate, DOR)  

In case they still think that this is valid I would encourage them to use the following much repeated example:
SELECT DATEDIFF(YEAR, '20121231', '20130101')

Clearly the output here cannot be used as an age.

The question is not the same as the difference I am looking for this time is using an update statement rather than a computed column.

Thanks for your effort

The question is not the same
I did not say it was.  I merely pointed out that your solution to calculate the age is flawed and it is surprising as Scott had already given you the correct solution, regardless of whether you use a computed column or not.

Gentlemen....Thank you