To find the total number of days

[SHARAD123]

Hi, Is there any way to find the total number of days in a month using the transformer stage?

[chulett]

Sure. How about subtracting the first day of the target date (internal format) from the first day of the next month, then subtract 1?

[kcbland]

Use a TIME dimension table and make one of its columns the number of days in the month of the date.

[SHARAD123]

I used the last_day(field name) fn of oracle in the sql space n got the number of days..

I ll try out your ideas too..
thnx anyway..

[gateleys]

As Kenneth suggested, you could use the TIME dimension. Assuming that you are developing a data warehouse, the model will always contain a TIME dimension. Further, it is a common practice to include the number of days in the month as a column in that dimension.

[chulett]

Yes, of course. I didn't pony that up (or the Oracle solution) as I thought the OP asked for a code solution 'using the transformer stage' and didn't mention the 'O' word.

Time to mark the thread as something - Resolved or Worked Around, regardless.

[ray.wurlod]

In that case - except for when the Pope fiddled with the calendar - you could use a simple mapping from month number (and year number in the case of February) to number of days in the month.

Code: Select all

Iconv(TheDate, "DM")                        ----->  svMonth
Iconv(TheDate, "DY")                        ----->  svYear
"31;28;31;30;31;30;31;31;30;31;30;31"       ----->  svMonthDaysList
Mod(svYear,4) = 0 And Mod(svYear,400) <> 0  ----->  svIsLeapYear 

Field(svMonthDaysList, ";", svMonth, 1) + svIsLeapYear

You *could* do that all in one expression; I feel that stage variables make it neater, more efficient (only need to calculate Iconv() once each) and easier to maintain.

[kcbland]

As Kenneth suggested, you could use the TIME dimension. Assuming that you are developing a data warehouse, the model will always contain a TIME dimension. Further, it is a common practice to include the number of days in the month as a column in that dimension.

A Time "table" should exist in every single business IMO. We used to call them a "CALENDAR" table back in my early days. An algorithm is worthless when companies have different "open for business" rules. Some honor one holiday and not the other, etc. It was intolerable for IT (or DP as we used to be called) to have to always update programs/common libraries with the latest information as for which days were exceptions to the rules in order to get numbers to turn out right. A simple calendar table makes everything easier, but seems to be out-of-fashion these days.

[chulett]

Totally agree, Ken, it should. When I was warehousing we always had one and it came in quite handy for things like you mentioned - all things date were answered there. However, recent efforts I've witnessed seemed to not even consider it. Damn kids.

The OP only asked for the total number of days in any given month (something suited to an algorithm) not anything like 'business days' or my answer would have closely mirrored yours.

Now, the 'content store' that is my life's work at the moment has no such beastie in it and no need for one, it seems. For whatever that is worth.