Converting the char 4 bytes to numeric

[nsubbarao]

Hi All,

I have a database field EFFECTIVE_DATE - Char 4 bytes and it has a value 9607 and my requirement goes like this.

I have to compare this EFFECTIVE_DATE against a value to check whether it is greater than 9607. I need to convert the EFFECTIVE_DATE to numeric and compare it against 9607. Could anyone please help me.

[chulett]

Just do the comparison. Server isn't picky about data types so no 'conversion' is necessary.

[nsubbarao]

Just do the comparison. Server isn't picky about data types so no 'conversion' is necessary. ...

I tried it but it is not giving me the desired output.

STW_EFFECTIVE_DATE - 9607

The condition goes like this :
If STW_EFFECTIVE_DATE > 9606
Then (STW_NEW_STAT_AREA2[1,48] : "1" : STW_NEW_STAT_AREA2[50,50])
Else STW_NEW_STAT_AREA2

When I give this condition I am not able to see the STW_NEW_STAT_AREA2 field populated with the desired value which is '1'

[chulett]

Not a conversion thing, I'd wager. What's in your AREA2 field? If either of those substrings return a null, all you'll end up with is a null after concatenation.

[nsubbarao]

Not a conversion thing, I'd wager. What's in your AREA2 field? If either of those substrings return a null, all you'll end up with is a null after concatenation. ...

STW_EFFECTIVE_DATE = 9607
STW_NEW_STAT_AREA2 = 92 5 40119500725114 012003031196607 19150000 9 0 000000002 0050

These are the input column values.
My desired output should be
STW_NEW_STAT_AREA2 = 92 5 40119500725114 012003031196607 19150000 9 01000000002 0050

[chulett]

Hmmm... ok. What if you quote the comparison?

Code: Select all

If STW_EFFECTIVE_DATE > "9606" Then

[nsubbarao]

Hmmm... ok. What if you quote the comparison?

Code: Select all

If STW_EFFECTIVE_DATE > "9606" Then

...

Let me try !!!

[nsubbarao]

Hmmm... ok. What if you quote the comparison?

Code: Select all

If STW_EFFECTIVE_DATE > "9606" Then

...

Let me try !!!

Still no luck chulette.