Month end dates validation

Latha1919 · Post by **Latha1919** » Sat Mar 08, 2008 10:55 pm

Hi,
As part of validations in a job, I have written the following code in a routine. When I tested with 02/30/2008 or 02/31/2008, its returning 3 for RPEmonth and 1,2 for RPEdate respectively.

RPEday = OConv(IConv(Field(ReportingPeriodEnd," ",1),"D/MDY[2,2,2]"),"DWB")
RPEdate = OConv(Iconv(Field(ReportingPeriodEnd," ",1),"D/MDY[2,2,2]"),"D-YMD[4,2,2]")[9,2]
RPEmonth = OConv(Iconv(Field(ReportingPeriodEnd," ",1),"D/MDY[2,2,2]"),"D-YMD[4,2,2]")[6,2]
RPEyear = OConv(Iconv(Field(ReportingPeriodEnd," ",1),"D/MDY[2,2,2]"),"D-YMD[4,2,2]")[1,4]

Please advise me on getting the correct date part and month part.

Appreciate your help.

ray.wurlod · Post by **ray.wurlod** » Sat Mar 08, 2008 11:12 pm

Not without more information.
1. What is the format of ReportingPeriodEnd?
2. What are you trying to achieve?
3. Have you investigated the MONTH.LAST Transform, rather than trying to re-invent the wheel?

Latha1919 · Post by **Latha1919** » Sun Mar 09, 2008 12:30 am

Well, we will be getting the data in the form of .csv files for daily/weekly/monthly/quarterly/yearly reporting spans. In these files we will have reporting period start and reporting period end dates. Dates will be in the form of either 'MM/DD/YY HH:MI:SS' or 'MM/DD/CCYY HH:MI:SS'. And these dates will be past dates & can be upto 2 years back. Based on the reporting duration value (daily/weekly/monthly/quarterly/yearly), we need to validate the reporting dates.
I didnt look into Month.Last transform yet.
Does this information help understand my complete requirement?

ray.wurlod · Post by **ray.wurlod** » Sun Mar 09, 2008 12:35 am

OK, you can use Field() to get the date portion from a timestamp and Iconv() to convert that to an internal format date. All you need for the second argument of Iconv() is "DMDY" - it's pretty clever about delimiters and number of digits in the year.

What you now need is a time dimension (hashed file) against which you can look up these past dates. It really is the best way and takes hardly any time to set up.

You still have not described what is involved in "validate".

Latha1919 · Post by **Latha1919** » Sun Mar 09, 2008 1:03 am

You still have not described what is involved in "validate".

Users will be submitting the metrics data in .CSV files. In our database, we have metrics data with the associated duration (weekly, monthly etc.) For each record of .CSV file, based on the metric name, we will arrive at the metric duration from the database. Reporting period span (difference of reporting period start and reporting period end dates) value will be compared with the metric reporting duration. eg:

For Weekly metrics data:
Both dates should not be future dates
Both dates should not be prior to two years
Reporting Period Start day should be Sunday
Reporting Period End day should be Saturday
Reporting Period End date = Reporting Period Start Date + 6
If the reporitng period month is spanning across two months, if the month is Feb and then consider 28, 29 days for Leap, Non-leap yr repectively
If the reporitng period month is spanning across two months, if the start month is not Feb, then consider 30 or 31 days based on the month.

Similarly for monthly, quarterly etc perform validations.

ray.wurlod · Post by **ray.wurlod** » Sun Mar 09, 2008 2:12 am

Do the individual tests in separate stage variables. Working in internal format, the "+6 days" test automatically handles leap year. These date arithmetic routines may prove useful.

Edit
I've just noticed that you are running version 5.x. The export file will probably not import, but the code is legal for version 5.x. In this case you might change the ServerVersion property in the DSX file.