Calendars and data formats

[stuwannop]

Hi

I am using a CRM table which houses contract records and I have a calendar script which links to the expiry date. I cannot get the dates to link together because of the source data.

When I pull through the data from CRM the "expiry date" is in the format dd/mm/yyyy hh:mm. This in itself does not cause the problem. The problem I have is in all the data the time is set to 23:59. So what I would see is 01/10/2011 23:59.

The calendar script (which I have pasted below) created a list of dates with 00:00 as the time.

I didn't think that would cause a problem but I think it does. The tables link together but there is no association. It might be a red herring but I don't think so.

Thanks for any help.

Range:
LOAD
 min(ConExpiryDate) as contractscalstartdate,
 max(ConExpiryDate) as contractscalenddate
resident CrmContracts;
//Peek out the values for later use
let vStart = peek('contractscalstartdate',-1,'Range')-1;
let vEnd = peek('contractscalenddate',-1,'Range');
let vRange = $(vEnd) - $(vStart);
//Remove Range table as no longer needed
Drop table Range;
//Generate a table with a row per date between the range above
ContractExpiryDateTable:
Load
 $(vStart)+recno() as ConExpiryDate
autogenerate $(vRange);
//Calculate the Parts you need to examine
ContractsExpiryCalendar:
load
 ConExpiryDate as ConExpiryDate,
// date(,'dd/mm/yyyy') as Cal_FullDate,
 Year(ConExpiryDate) as ConCalendarYear,
 'Q'&ceil(Month(ConExpiryDate)/3) AS ConCal_Quarter,
// right(yearname(Date,0,$(vFiscalMonthStart)),4) as Cal_FiscalYear,
// if(InYear (Date, $(vToday), -1),1) as Cal_FULL_LY, // All Dates Last Year
// if(InYear (Date, $(vToday), 0),1) as Cal_FULL_TY, // All Dates This Year
    if(InYearToDate (ConExpiryDate, today(), 0),1,0) as ConCal_YTD_TY,  // All Dates to Date this Year
    if(InYearToDate (ConExpiryDate, today(), -1),1,0) as ConCal_YTD_LY,  // All Dates to Date Last Year
    
    if(InQuarterToDate (ConExpiryDate, today(), 0),1,0) as ConCal_QTR_TQ,
    if(InQuarterToDate (ConExpiryDate, today(), -1),1,0) as ConCal_QTR_LQ,  
    
    if(InMonthToDate(ConExpiryDate, today(), 0),1,0) as ConCal_MNTH_TM,
    if(InMonthToDate(ConExpiryDate, today(), -1),1,0) as ConCal_MNTH_LM,  
    
//  YTD_LY, used in Expressions Ex. Sum(Sales*YTD_LY)
 quartername(ConExpiryDate) as ConCal_CalendarQuarter,
// quartername(Date,0,$(vFiscalMonthStart)) as Cal_FiscalQuarter,
// Month(Date)&'-'&right(yearname(Date,0,11),4) as Cal_FiscalMonthYear, //Fiscal!
 Month(ConExpiryDate)&'-'&right(year(ConExpiryDate),4) as ConCal_MonthYear,
 Month(ConExpiryDate) as ConCal_Month,
 Day(ConExpiryDate) as ConCal_Day,
 Week(ConExpiryDate) as ConCal_Week,
 Weekday(ConExpiryDate) as ConCal_WeekDay
resident ContractExpiryDateTable;
//Tidy up
Drop table ContractExpiryDateTable;