I have also tried the below solution I found on the forums.
[Fiscal Calendar]: LOAD date(date#(20061231,'YYYYMMDD')+recno(),'MM/DD/YY') as "invoice_date" AUTOGENERATE today()-date#(20061231,'YYYYMMDD') ; LEFT JOIN ([Fiscal Calendar]) LOAD "invoice_date" ,date(monthstart(invoice_date),'MMM YY') as "Month Fisical" ,date(yearstart(invoice_date),'YYYY') as "Calendar Year" ,date(yearstart(invoice_date,0,4),'YYYY') as "Fiscal Year" RESIDENT [Fiscal Calendar] ;
This solution worked better but not all my invoice_dates are showing up. My invoice_date values from the database show up in the YYYY-MM-DD TT:TT:TT.TTT format. I would say it is was a date format conversion issue but the above code 'YYYYMMDD' does convert SOME of the date values correctly and others it does not convert and thus are not displayed. I do not understand why and can not find a pattern.
Thanks for any help on the above or other methods of doing this.