Complex Date Conversion Problem

[bmsq]

Hi guys,

I've got a problem with converting dates in either a Column Import or Transformer that I hope someone will be able to help me out with.

The input date string is %dd %mmm and I need to convert this to a Date type using a second date as the cut off period. My initial thought was to get the year from the cut off date and append it to the source string to get %dd %mmm %yyyy. This doesn't work as I need to check if the source %dd %mmm is before the cut off period, in which case the appended year would be one year lower than that of the cut off date.

I also though that I could append the the cut off year to the source date and then convert is to a Date. This date would not be correct, but I could use it to check if it was before or after the cut off date. If before, then I use the cut off year-1. However, I think this would cause conversion errors when if the date was 29 Feb because this is only valid on a leap year.

I hope that make sense. Any help would be appreciated.

[ray.wurlod]

First please make clear whether this is a server job question (as marked) or a parallel job question (as per the forum in which you have posted). The answer will be markedly different based on job type. If it's in a job sequence, let us know that too.

[bmsq]

Server? How did that get in the title, I selected parallel....

Anyway, sorry about the confusion. This is a Parallel problem, I'd just use DS Basic or OCONV/ICONV if this were server.

Cheers,
Barry

[JoshGeorge]

Convert your InDate and CutOffDate to this format - %mm%dd (mmdd - string). Now follow the below logic in stage variable:

If MMDD of InDate is greater or equal to MMDD of CutOffDate then append CCYY else append CCYY - 1

[bmsq]

Thanks Josh,

I thought that might be the way I'd need to do it. Is there any easy way to perform the conversion from %dd %mmm to %dd %mm without writing some huge if statement in a transformer to convert the month?

Cheers,
Barry

[JoshGeorge]

You have to convert %mm%dd (mmdd - string). Other way it will be wrong results.

...the conversion from %dd %mmm to %dd %mm..

[JoshGeorge]

Try StringToDate function
Ex:
StringToDate('Mar 02 2003',"%mmm %dd %yyyy")
Will give you - "2003-03-02"

For the year part you can append a dummy year to your InDate field.

[bmsq]

Josh,

When using a dummy year, do I need to ensure that I choose a leap year to ensure that "29 Feb" is valid?

Thanks,
Barry

[JoshGeorge]

Missed that point. You have to use a leap year to ensure "29 Feb" is valid.