string to date conversion

HemaV · Posted: Fri Aug 31, 2012 11:16 pm

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 · Posted: Sat Sep 01, 2012 8:03 am

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 i ...

What's this?

rajnishrajtomar · Posted: Sat Sep 01, 2012 3:00 pm

please provide more clarification on your logic.

HemaV · Posted: Sun Sep 02, 2012 12:27 am

I need a string not a date

ray.wurlod · Posted: Sun Sep 02, 2012 1:34 am

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 de ...

What's this?

rajnishrajtomar · Posted: Sun Sep 02, 2012 6:38 am

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 · Posted: Sun Sep 02, 2012 7:00 am

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

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 · Posted: Sun Sep 02, 2012 7:06 am

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.