How to convert mmddyy date format to yyyy-mm-dd

[swerajan]

Hi
I am getting a date in the format of mmddyy. I need to convert it to yyyy-mm-dd format. How can i achieve this?

[chulett]

You need to convert it into another string in that format, or you need to convert it into a date? There's a difference.

[ray.wurlod]

Dates don't have formats - they are binary. So what is your source? If it is a text file, for example, you have a string but, if you have specified a data type of Date, then the import operator will convert this to binary format based on the format string specified in your record schema.

The only time you need to convert this to a different format is if you are going to export it as a string data type (for example Char or VarChar).

You also need a business rule concerning your century pivot - which century applies given that you have only a two digit year component.

[swerajan]

@ Chulett- I need to convert the string in the format of mmddyy to date of fomat yyyy-mm-dd

I am loading it into the table which has date in the above mentioned format. The source is mainframes here.

[ray.wurlod]

You can re-arrange (concatenate) the relevant substrings. You also need logic to add the century number according to your own (century pivot) business rules.

A less efficient approach would be to convert the source string into a date then to convert that date back to a string, with a different format specified in each case.

[Kayalbaskaran]

The below conversion would help you in converting the string to required date format

StringToDate(Input Column,"%mm%dd%1950yy")

[ray.wurlod]

No it won't. It will convert the string to a binary date. That's only half way to the required solution. You're also making an assumption about the century pivot year.