string to date conversion

[HemaV]

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

[chulett]

First we need to note yet again that "08-08-2012" is not a date, it a string - an external representation of a date. So, first question is do you need an actual Date datatype or a String in that specific format?

[rajnishrajtomar]

please provide more clarification on your logic.

[HemaV]

I need a string not a date

[ray.wurlod]

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.

[rajnishrajtomar]

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

[chulett]

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

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.

[chulett]

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.