Date conversion

[rajkraj]

I have a requirement where in i have to convert mmddyy date format into YYYYMMDD
for example the date would be 070307 the output should be 20070307,
if the date is 012975 the output should be 19750129.
I have to compare the last 2 charaters from input(yy) if it is greater than 10
it has to be appended by 1975 or else append it with 20 i.e 2007.
The input column datatype is Numeric(coming in from a cobol copy book) and output column is number.

I am using the below

If Right(Col1,2) >10 then
DateToString(StringToDate(Col1,"%mm%dd%yy"),"%yyyy%mm%dd") else DateToString(StringToDate(col1,"%mm%dd%2000yy"),"%yyyy%mm%dd")

But i see that the even when Right(Col1,2) is less than 10(07) it always shows 19070307,where
it should be 20070307.

Is my approach correct or is there a better way to do this.
Kindly shed light on this.

Thanks

[ArndW]

Code: Select all

If Right(Col1,2) >'10' then '20':In.Col1[5,2]:In.Col1[3,2]:In.Col[1,2] ELSE '19':In.Col1[5,2]:In.Col1[3,2]:In.Col[1,2]

Simpler since both input and output are strings.

[AmeyJoshi14]

Hi,
You can use stage variable for this,
Right(Col1,2) ---->svrValue(type interger)
Then you can compare this stage variable(svrValue) with "10".

[AmeyJoshi14]

Hi,
You can use stage variable for this,
Right(Col1,2) ---->svrValue(type interger)
Then you can compare this stage variable(svrValue) with "10".

[ArndW]

Amey - if you use a stage variable that is integer and involves and implicit conversion, then you need to compare to the constant 10, not "10" ; for that reason I kept the substring and compared to a string constant of "10" to avoid unnecessary datatype conversions.

[AmeyJoshi14]

Amey - if you use a stage variable that is integer and involves and implicit conversion, then you need to compare to the constant 10, not "10" ; for that reason I kept the substring and compared to a ...

Oops! sorry for that

[ArndW]

No worries, but I had to state it, since if IntSvar = 3 then

Code: Select all

IntSvar > "10"

evaluates to true when the integer is converted to a string and get the value "3"

[rajkraj]

I am doing the below in the stage variable before doing this comparision

STR('0',6-Len(Trim(col1))) :Trim(col1) and then i have to implement the logic for Date,
And the stage variable is of type Varchar.
Now how my comparision should be as below

If Right(svDt1,2) >'10' then '19':svDt1[5,2]:svDt1[3,2]:svDt1[1,2] ELSE '20':svDt1[5,2]:svDt1[3,2]:svDt1[1,2]

Correct me if i am wrong.

Thanks