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


ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

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