Extracting Long Month Name from Date

Seyed · Post by **Seyed** » Thu Jul 28, 2011 1:16 pm

Hi all,
I have a DataStage Server job. I want to extract a month's full name, example, January, February, etc from it. What is the best way to do this?

Out of desperation, I tried the following function, but learned from one of Craig's postings, that this format string belongs to parallel jobs.

Code: Select all

DateToString(DSLink3.LOG_DT_TM,"%mmmm")

I suppose, I could create a stage variable and get the 2 digit month by doing something like:

Code: Select all

DSLink3.LOG_DT_TM[6,2]

and then use a nested if statement (if it is allowed) in the transform to build the long name for the month.

Thank you for your help,

Seyed

chulett · Post by **chulett** » Thu Jul 28, 2011 1:58 pm

For Server there are the all powerful "Conv" functions:

Code: Select all

OConv(OConv(IConv(YourField,"D"),"DMA"),"MCT")

DMA will get you the Alpha Month name in CAPS and MCT will then case it as you wanted, just like using the Capitals() transform would. If your date includes the time you'll need to perform this just on the date portion.

Now, I know there's a way to do both OConv transformations at once but don't recall the syntax off the top of my head. Ray posts it regularly... value mark separated?

ray.wurlod · Post by **ray.wurlod** » Thu Jul 28, 2011 4:36 pm

Code: Select all

OConv(IConv(YourField,"D"),"DMA" : @VM :"MCT")

chulett · Post by **chulett** » Thu Jul 28, 2011 4:54 pm

Seyed · Post by **Seyed** » Mon Aug 01, 2011 12:40 pm

Hi Craig,
Thank you for your help. I tried the following code, but didn't get anything loaded into the table. The recieving column remains empty and no errors are generated. I even tried writing the result to a sequential file, the same result.

Code: Select all

OConv(OConv(IConv(DSLink1.LOG_DT_TM,"D"),"DMA"),"MCT")

Any ideas why this code is not working?

Thanks again,

Seyed

Seyed · Post by **Seyed** » Mon Aug 01, 2011 12:45 pm

Hello Ray,
I tried the following code, it didn't populate the column.

Code: Select all

OConv(IConv(DSLink1.LOG_DT_TM,"D"),"DMA" : @VM :"MCT")

Am I missing something here?

Thank you very much,

Seyed

chulett · Post by **chulett** » Mon Aug 01, 2011 2:06 pm

Post some examples of what your incoming date values look like.

Seyed · Post by **Seyed** » Mon Aug 01, 2011 4:01 pm

Hi Craig,
Thank you for your help. Below is a sample of date coming in:

Code: Select all

2011-04-10 00:00:36
2011-04-10 00:02:45
2011-04-10 00:03:48
2011-04-10 01:23:28

Thanks again,

Seyed

chulett · Post by **chulett** » Mon Aug 01, 2011 4:33 pm

Those aren't dates, those are timestamps which is why the code is not working. You need to ditch the time portion before what I posted will work.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 02, 2011 12:07 am

This might do it for you, then.

Code: Select all

OConv(IConv(DSLink1.LOG_DT_TM, "G0 1" : @VM : "D"), "DMA" : @VM :"MCT")

chulett · Post by **chulett** » Tue Aug 02, 2011 7:03 am

Interesting... I would have just done a substring or a field, never really had much need to figure out what the "Group" conversions do. Is the full syntax for that code documented somewhere? All the Server Job Developer's Guide has are two example calls and nothing else. It seems to be akin to using Field...

Seyed · Post by **Seyed** » Tue Aug 02, 2011 7:42 am

Hello Craig and Ray,

I just finished trying the following methods none of which worked. I am pretty sure that with the first two methods, the time has been removed from the timestamp.

Code: Select all

OConv(OConv(IConv(Field(DSLink1.LOG_DT_TM, " ", 1,1),"D"),"DMA"),"MCT") 
OConv(OConv(IConv(DSLink1.LOG_DT_TM[1,10],"D"),"DMA"),"MCT") 
OConv(IConv(DSLink1.LOG_DT_TM, "G0 1" : @VM : "D"), "DMA" : @VM :"MCT")

I don't know if it makes any difference, but I also tried using the following routine to remove time from the timestamp,

Code: Select all

DateTimeStampToODBC(DSLink1.LOG_DT_TM)

This routine resulted in dates to appear as follows:

Code: Select all

2011--0-4-
2011--0-4-
2011--0-4-

As a workaround, I have built an Oracle table containing 12 rows one for each month. I am using this table for a lookup in the DS Designer until this problem is resolved.

Thank you both very much,

Seyed

chulett · Post by **chulett** » Tue Aug 02, 2011 7:56 am

You need to let us know more than just "none of which worked".

Spend some time troubleshooting the individual conversions. What I would do in your shoes to make sure this was working would be either:

1. Use stage variables to break this up into individual conv calls, do this one step at a time. Debug the job and see where it goes wrong.

2. Build a server routine which just takes your timestamp in. Call each function individually and pass the "Ans" back out with a delimited string that contains all of the intermediate results. That makes it trivial to "Test" the routine, check the output, tweak the code, lather-rinse-repeat until it works.

Once corrected, you can go back to your corrected single-line conversion if desired.

Seyed · Post by **Seyed** » Tue Aug 02, 2011 8:03 am

It sounds like a great plan. I'll see if I could isolate this problem following your suggestions and let you when I identify the problem.

Thank you very much,

Seyed

Seyed · Post by **Seyed** » Tue Aug 02, 2011 10:26 am

Craig,
The problem is resolved. I followed your instructions and identified that the problem was in the Iconv routine. It turns out that I had to remove dashes from the date.

Here is how I got your suggested code to work:

Code: Select all

OConv(OConv(IConv(Change(DSLink1.LOG_DT_TM[1,10],"-",""),"D"),"DMA"),"MCT")

I ended up removing time and also dashes from date.

Thank you so much,

Seyed