Trouble with Master Calendar Time Date

[provie98]

I'm trying to create a master calendar table to attached to an Add_Date that I have in my Core_Activity Table.

I have the following script to do this:

LET min_calendar_date = Num(Floor(Peek('Add_Date',0,'CORE_ACTIVITY')));
LET max_calendar_date = Num(Floor(Peek('Add_Date',-1,'CORE_ACTIVITY')));
LET varToday = num(today());

TempCalendar:
LOAD
  $(min_calendar_date) + rowno() - 1 as Num,
  date($(min_calendar_date) + rowno() - 1) as TempDate
AUTOGENERATE
  $(max_calendar_date) - $(min_calendar_date) + 1;

Master_Calendar:

LOAD
TempDate as Add_Date,
Week(TempDate)as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
Weekday(TempDate) as WeekDay,
ceil(month(TempDate)/3) as Quarter,
'Q' & ceil(month(TempDate)/3) as QuarterName,
Date(monthstart(TempDate),'MMM-YYYY') as MonthYear,
Week(TempDate) & '-' & Year(TempDate) as WeekYear,
inyeartodate(TempDate,$(varToday),0)*-1 As CurYTDFlag,
inyeartodate(TempDate,$(varToday),-1)*-1 as LastYTDFlag
RESIDENT TempCalendar

Order by TempDate ASC;
Drop Table TempCalendar; 

The problem I'm encountering is that my Add_Date has a time on it.  When I select Year off of the Master_Calendar table, it is only bringing back those dates time stamped of 12:00:00 AM.  Any idea how i can get it to bring back all the dates for a specific Year regardless of the time?  Any advice is greatly appreciated.

Thanks!

[#2]

Change the line

TempDate as Add_Date,

with

date(TempDate) as Add_Date,

Hope this helps

[its_anandrjs]

Hi,

In your TempCalendar table in below line change date script like

  date($(min_calendar_date) + rowno() - 1,'MM/DD/YYYY' ) as TempDate

put your region date format there

Hope this helps

Thanks & Regards

[its_anandrjs]

Another way is change script in Master_Calendar

Master_Calendar:

LOAD
Date(TempDate,'MM/DD/YYY') as Add_Date,

Hope this Helps

Thanks & Regards

[provie98]

I tried all of the above suggestions and it's still not working.  I appreciate the input though.  If anyone else thinks of something let me know.  Thanks!

[DavidFoster1 · Accepted solution]

In your Core_Activity table add an extra field (cal_date or somesuch) that is derived from the add_Date using:

daystart(add_date) note: dont use DATE function as that returns a string value.

Then use that date to build your calendar.

[Anonymous]

Hi

The Master_Calendar script all looks fine to me, but I think David above is sort of on the correct track.

As he says add an extra field in your script to the Core_Activity table, I'd suggest :

     num(floor(add_date))          as cal_date ,

Then in your Master_Calendar script change this line :

     TempDate as Add_Date,

to this

     TempDate as cal_date ,

After a reload check in your table viewer that Master_Calendar is joining to Core_Activity via cal_date and not by anything else.

Best Regards,     Bill

[provie98]

Thanks so much for your help.  This suggestion actually worked for me.