Date format conversion

Amit Jaiswal · Post by **Amit Jaiswal** » Mon Jul 11, 2005 5:57 am

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

ray.wurlod · Post by **ray.wurlod** » Mon Jul 11, 2005 6:00 am

You've missed the "%" signs on the date format descriptors. Try

Code: Select all

DateToString(StringToDate(Trim(myDate), "%dd/%mm/%yyyy"), "%mm/%dd/%yyyy")

And, of course, for this to work your input column (myDate) must be a string.

Amit Jaiswal · Post by **Amit Jaiswal** » Tue Jul 12, 2005 3:39 am

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

ray.wurlod · Post by **ray.wurlod** » Tue Jul 12, 2005 3:50 am

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

Amit Jaiswal · Post by **Amit Jaiswal** » Thu Jul 14, 2005 4:43 am

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

ray.wurlod · Post by **ray.wurlod** » Thu Jul 14, 2005 5:04 am

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.

Amit Jaiswal · Post by **Amit Jaiswal** » Fri Jul 15, 2005 3:04 am

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

divine · Post by **divine** » Fri Jul 15, 2005 3:17 am

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.

pnchowdary · Post by **pnchowdary** » Fri Jul 15, 2005 9:54 am

Hi Amit,

You can use the following logic

Code: Select all

Ans = Arg1[4,2]:"/":Arg1[1,2]:"/":Arg1[7,4]

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