Create date field from substring of text field

I am trying to populate a field in a SQL table based on the values
returned from using substring on a text field.

Example:

Field Name = RecNum
Field Value = 024071023

The 7th and 8th character of this number is the year. I am able to
get those digits by saying substring(recnum,7,2) and I get '02'. Now
what I need to do is determine if this is >= 50 then concatenate a
'19' to the front of it or if it is less that '50' concatenate a '20'.
This particular example should return '2002'. Then I want to take the
result of this and populate a field called TaxYear.

Any help would be greatly apprecaietd.

MarkMark,

Assuming both RecNum and TaxYear fields are in the same table, you can use
this script to populate TaxYear:

update YourTable
set TaxYear = case
when SubString(RecNum,7,2) >= '50' then '19' +
SubString(RecNum,7,2)
else '20' + SubString(RecNum,7,2)
end

Shervin

"Mark" <markcash@.Hotmail.com> wrote in message
news:57bdc737.0310151257.1dc4d0a9@.posting.google.c om...
> I am trying to populate a field in a SQL table based on the values
> returned from using substring on a text field.
> Example:
> Field Name = RecNum
> Field Value = 024071023
> The 7th and 8th character of this number is the year. I am able to
> get those digits by saying substring(recnum,7,2) and I get '02'. Now
> what I need to do is determine if this is >= 50 then concatenate a
> '19' to the front of it or if it is less that '50' concatenate a '20'.
> This particular example should return '2002'. Then I want to take the
> result of this and populate a field called TaxYear.
> Any help would be greatly apprecaietd.
> Mark|||This work exaclty like I was wanting!!!

Thanks for the advice Shervin!!

Mark

"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vorem27pdlp2a9@.corp.supernews.com>...
> Mark,
> Assuming both RecNum and TaxYear fields are in the same table, you can use
> this script to populate TaxYear:
> update YourTable
> set TaxYear = case
> when SubString(RecNum,7,2) >= '50' then '19' +
> SubString(RecNum,7,2)
> else '20' + SubString(RecNum,7,2)
> end
> Shervin
> "Mark" <markcash@.Hotmail.com> wrote in message
> news:57bdc737.0310151257.1dc4d0a9@.posting.google.c om...
> > I am trying to populate a field in a SQL table based on the values
> > returned from using substring on a text field.
> > Example:
> > Field Name = RecNum
> > Field Value = 024071023
> > The 7th and 8th character of this number is the year. I am able to
> > get those digits by saying substring(recnum,7,2) and I get '02'. Now
> > what I need to do is determine if this is >= 50 then concatenate a
> > '19' to the front of it or if it is less that '50' concatenate a '20'.
> > This particular example should return '2002'. Then I want to take the
> > result of this and populate a field called TaxYear.
> > Any help would be greatly apprecaietd.
> > Mark