Page 1 of 2
Extracting Long Month Name from Date
Posted: Thu Jul 28, 2011 1:16 pm
by Seyed
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:
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
Posted: Thu Jul 28, 2011 1:58 pm
by chulett
For Server there are the all powerful "Conv" functions:
Code: Select all
OConv(OConv(IConv(YourField,"D"),"DMA"),"MCT")
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?
Posted: Thu Jul 28, 2011 4:36 pm
by ray.wurlod
Code: Select all
OConv(IConv(YourField,"D"),"DMA" : @VM :"MCT")
Posted: Thu Jul 28, 2011 4:54 pm
by chulett
Posted: Mon Aug 01, 2011 12:40 pm
by Seyed
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
Posted: Mon Aug 01, 2011 12:45 pm
by Seyed
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
Posted: Mon Aug 01, 2011 2:06 pm
by chulett
Post some examples of what your incoming date values look like.
Posted: Mon Aug 01, 2011 4:01 pm
by Seyed
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
Posted: Mon Aug 01, 2011 4:33 pm
by chulett
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.
Posted: Tue Aug 02, 2011 12:07 am
by ray.wurlod
This might do it for you, then.
Code: Select all
OConv(IConv(DSLink1.LOG_DT_TM, "G0 1" : @VM : "D"), "DMA" : @VM :"MCT")
Posted: Tue Aug 02, 2011 7:03 am
by chulett
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...
Posted: Tue Aug 02, 2011 7:42 am
by Seyed
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:
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
Posted: Tue Aug 02, 2011 7:56 am
by chulett
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.
Posted: Tue Aug 02, 2011 8:03 am
by Seyed
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
Posted: Tue Aug 02, 2011 10:26 am
by Seyed
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
