DSXchange

Hi,
I am using sequential file as source with char datatype and dataset as target file. I want to convert date format from dd/mm/yyyy to mm/dd/yyyy. I have tried using DateToString(StringToDate(Trim(myDate), "dd/mm/yyyy"), "mm/dd/yyyy") function but conversion is not proper. My understanding is that, for inner function it needs date format of input date and for outer function we have to give required date format. The output is '******'.
Then I have just tried with inside function by changing Project level and job level default date format to mm/dd/yyyy but output coming is yyyy-mm-dd. Please give me solution for this.
Thanks in advance.
-Amit

You've missed the "%" signs on the date format descriptors. Try And, of course, for this to work your input column (myDate) must be a string.

Hi Ray,

Thanks for your reply. I am sorry, it was typo while posting this question, I have used correct format only. But still I am getting '****' in the target. I am getting correct result if target datatype is varchar(10), but when I make it to date this problem occurs. Is it because I am trying to save string in date column? If it is the case what is the solution to get correct date format? Even I have tried to use Left function and concatenate it and store it but result is ok when I am storing it in varchar(10) and problem occurs with date type.
Please help.

-Amit

What is the data type of column myDate in your example?

Hi Ray,
Data type of MyDate is date with width 10. This whole conversion works file if I change MyDate datatype to varchar(10).
Please clarify.
Thanks,
-Amit

Trim(myDate) is illegal when myDate has data type Date. Similarly, StringToDate() expects a string argument, not a date argument.
That's why I asked about data type.
The next recommendation was going to be to change the data type to string (VarChar or Char) to see if that worked. Obviously it does.

Hi Ray,
Trim(myDate) I used as from source this data is coming in char format. But in target I need only date data type. As you said I can't use DateToString() for date format then what is a solution to get required date format in Dataset file.
Thanks,
-Amit

Hi Amit,

Can you give your input. Like what is the format. For example do u get the input like 01-Jan-2005 or 01-01-2005 or what is the format and what should be your out put. Can you give example.

Hi Amit,

You can use the following logic
where Arg1 is the date in dd/mm/yyyy format

I always use this logic in my routine to convert date to US format (mm/dd/yyyy) and It works like a charm.

Thanks,
Naveen