string to date conversion
Moderators: chulett, rschirm, roy
string to date conversion
I've a varchar field with value 8/8/2012 now I want to convert it to 08-08-2012. when i'm trying to convert it to date using string to date function i'm not able to view the data.please provide me the logic to achieve the above..thanks in advance
-
- Participant
- Posts: 2
- Joined: Thu Apr 12, 2012 8:28 am
- Location: India
string to date conversion
I need a string not a date
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
StringToDate() requires a format string that exactly matches the format in which the date is contained within your string. So does DateToString() to get the date into a different format. Even the delimiter characters have to be correct.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 2
- Joined: Thu Apr 12, 2012 8:28 am
- Location: India
Use following function in sequence, it may help you
StringToDate(input.mystring,"%dd/%mm/%yyyy"): it will give you dd-mm-yyyy
DateToString(output.mystring): retun a string in dd-mm-yyyy
otherwise, you can use following logic also:
I feel your date is coming like 8/8/2012,9/12/2012,21/8/2012,21/12/2012 and you need 08-08-2012, 09-12-2012,21-08-2012,21-12-2012 respectively.
Take three stage variables:
svar1: if input.mystring[2,1]='/' Then '0':input.mystring[1,1] Else if input.mystring[3,1]='/'
Then input.mystring[1,2] Else ''
svar2: if input.mystring[4,1]='/' Then '0':input.mystring3,1] Else if ( input.mystring[2,1]='/' and input.mystring[5,1]='/') Then input.mystring[3,2] Else If ( input.mystring[3,1]='/' and input.mystring[5,1]='/') Then '0':input.mystring[4,1] Else If input.mystring[6,1]='/'
Then input.mystring[4,2] Else ''
svar3: if input.mystring[4,1]='/' Then input.mystring[5,4] Else if input.mystring[5,1]='/'
Then input.mystring[6,4] Else if input.mystring[6,1]='/'
Then input.mystring[7,4] Else ''
Output column: svar1:'-': svar2: '-': savr3
StringToDate(input.mystring,"%dd/%mm/%yyyy"): it will give you dd-mm-yyyy
DateToString(output.mystring): retun a string in dd-mm-yyyy
otherwise, you can use following logic also:
I feel your date is coming like 8/8/2012,9/12/2012,21/8/2012,21/12/2012 and you need 08-08-2012, 09-12-2012,21-08-2012,21-12-2012 respectively.
Take three stage variables:
svar1: if input.mystring[2,1]='/' Then '0':input.mystring[1,1] Else if input.mystring[3,1]='/'
Then input.mystring[1,2] Else ''
svar2: if input.mystring[4,1]='/' Then '0':input.mystring3,1] Else if ( input.mystring[2,1]='/' and input.mystring[5,1]='/') Then input.mystring[3,2] Else If ( input.mystring[3,1]='/' and input.mystring[5,1]='/') Then '0':input.mystring[4,1] Else If input.mystring[6,1]='/'
Then input.mystring[4,2] Else ''
svar3: if input.mystring[4,1]='/' Then input.mystring[5,4] Else if input.mystring[5,1]='/'
Then input.mystring[6,4] Else if input.mystring[6,1]='/'
Then input.mystring[7,4] Else ''
Output column: svar1:'-': svar2: '-': savr3
Sorry, but the above is incorrect in more ways than one. Please read what Ray posted for the 'why' of that. The rest of the advice is unnecessarily complex, I'd suggest looking into the Field() function as a start to simplify all that.rajnishrajtomar wrote:Use following function in sequence, it may help you
StringToDate(input.mystring,"%dd/%mm/%yyyy"): it will give you dd-mm-yyyy
DateToString(output.mystring): retun a string in dd-mm-yyyy
Last edited by chulett on Sun Sep 02, 2012 7:06 am, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
You'll need a nested pair of conversions to get that string, first StringToDate() and then DateToString(). As Ray noted, you'll need to use the proper format string based on the what the string looks like (or needs to look like) in each function call.
If you are still having problems, post the specific syntax you are using.
I would also suggest checking the Parallel Job Developer's Guide pdf for the Date Formats section, specifically the options listed below Table 6. The first one listed ("s") is a very good one to become familiar with.
If you are still having problems, post the specific syntax you are using.
I would also suggest checking the Parallel Job Developer's Guide pdf for the Date Formats section, specifically the options listed below Table 6. The first one listed ("s") is a very good one to become familiar with.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers