Link to home
Create AccountLog in
Avatar of Richard
RichardFlag for United States of America

asked on

Update SQL table with year value from partial year text field

I have an MSSQL table called Members.  Two fields (columns) in the table are MemberSince and MemberNumber.  I need to fill the MemberSince field with the year the person joined the association.  The MemberNumber field value is a text string in the for of YY/xxx - where YY is the last two digits of the year joined and the xxx is a sequential number starting at 1 for the first person to join in any given year.  The year part starts in 93 (e.g. 1993).  I want to fill the MemberSince field with the full 4 digit year from the year part of the MemberNumber.  The MemberNumber is from 93 - 23 (1993 through 2023).

I want a T-SQL query to update the Members table.

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

See if this works for you:
update members
set membersince = datepart(year, cast('01/01/'+left(membernumber,2) as date));

Open in new window

you are making the same problem that we had to solve in Y2K 

define a minimum year i.e. 1989 so split out the 2 digit year

if this is less than  90 then year is 2000 + year else year is 1900+ year

Avatar of Qlemo
Flag of Germany image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account