Create date field from substring of text field

[Mark]

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

[Shervin Shapourian]

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" <ma******@Hotmail.com> wrote in message
news:57**************************@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

[Mark]

This work exaclty like I was wanting!!!

Thanks for the advice Shervin!!
Mark
"Shervin Shapourian" <Sh**********@hotmail.com> wrote in message news:<vo************@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" <ma******@Hotmail.com> wrote in message
news:57**************************@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