Date conversion
Moderators: chulett, rschirm, roy
Date conversion
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
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
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]
-
- Participant
- Posts: 334
- Joined: Fri Dec 01, 2006 5:17 am
- Location: Texas
Hi,
You can use stage variable for this,
Right(Col1,2) ---->svrValue(type interger)
Then you can compare this stage variable(svrValue) with "10".![Smile :)](./images/smilies/icon_smile.gif)
You can use stage variable for this,
Right(Col1,2) ---->svrValue(type interger)
Then you can compare this stage variable(svrValue) with "10".
![Smile :)](./images/smilies/icon_smile.gif)
http://findingjobsindatastage.blogspot.com/
Theory is when you know all and nothing works. Practice is when all works and nobody knows why. In this case we have put together theory and practice: nothing works. and nobody knows why! (Albert Einstein)
Theory is when you know all and nothing works. Practice is when all works and nobody knows why. In this case we have put together theory and practice: nothing works. and nobody knows why! (Albert Einstein)
-
- Participant
- Posts: 334
- Joined: Fri Dec 01, 2006 5:17 am
- Location: Texas
Hi,
You can use stage variable for this,
Right(Col1,2) ---->svrValue(type interger)
Then you can compare this stage variable(svrValue) with "10".![Smile :)](./images/smilies/icon_smile.gif)
You can use stage variable for this,
Right(Col1,2) ---->svrValue(type interger)
Then you can compare this stage variable(svrValue) with "10".
![Smile :)](./images/smilies/icon_smile.gif)
http://findingjobsindatastage.blogspot.com/
Theory is when you know all and nothing works. Practice is when all works and nobody knows why. In this case we have put together theory and practice: nothing works. and nobody knows why! (Albert Einstein)
Theory is when you know all and nothing works. Practice is when all works and nobody knows why. In this case we have put together theory and practice: nothing works. and nobody knows why! (Albert Einstein)
-
- Participant
- Posts: 334
- Joined: Fri Dec 01, 2006 5:17 am
- Location: Texas
Oops! sorry for thatArndW wrote: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 ...
![Embarassed :oops:](./images/smilies/icon_redface.gif)
http://findingjobsindatastage.blogspot.com/
Theory is when you know all and nothing works. Practice is when all works and nobody knows why. In this case we have put together theory and practice: nothing works. and nobody knows why! (Albert Einstein)
Theory is when you know all and nothing works. Practice is when all works and nobody knows why. In this case we have put together theory and practice: nothing works. and nobody knows why! (Albert Einstein)
No worries, but I had to state it, since if IntSvar = 3 then evaluates to true when the integer is converted to a string and get the value "3"
Code: Select all
IntSvar > "10"
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
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