Date conversion YYYY-MM-DD to MM/DD/YYYY

[sam334]

All,
Need a help in date conversion. I know this probably has been discussed before but could not figure it out.

Here, the input column and output column both are DATE data type. Input is '2014-12-10' and out required is 12/10/2014.

We have the server version where we use, ICONV(Column1,"D4-YMD")

Not sure how to proceed in parallel.

I tried using,, Column1[6,2]:'/':Column1[9,2]:'/':Column1[1,4] But not working. Any thoughts..

Thanks..

[chulett]

Sorry but as we've discussed here quite a bit, a DATE field does not have a format. It's only when you pull it out into a string that you apply an external format to it, two of which you have shown. Meaning, there is only a conversion required when a string is involved... not two DATE fields.

So, what's really going on? What is driving your 'requirement' for these formats?

[ray.wurlod]

What are your source and target? What data types pertain to each for these "date" fields.

If Date, there's nothing to do.
If a string of some kind, then you have some flexibility.

Read up about format strings for date functions.

[sam334]

Ray,
Input is delimiter file and output is DB2. So, basically its a huge file and put in unix server as .DEL. we pull it up from there using FTP plug in. And data type for input and output is DATE.

[chulett]

Input is delimiter file

Then your data type for input is a string. If you are able to successfully read it as a DATE then there's no conversion to do as the stage has already done that for you. Failing that, use the StringToDate() function.

[ray.wurlod]

You can try declaring the metadata for the input file as data type string (that is, SQL data type Char), with an appropriate format string in the extended properties if YYYY-MM-DD is not your system's default format. This will read the date and convert it to a binary date type, which should be able to be fed directly to a DB2 date type column.

[sam334]

Thanks Ray and Craig. I will try and update it asap.