2 Replies Latest reply: Jan 30, 2012 6:08 AM by niavasha RSS

    Large year ranges

      Hi All

       

      I'm trying to handle a data set that has data going back for a 1000 years.

       

      I want to generate a master calendar that covers all dates off from the beginning

      of my data set to the end.

       

      When trying to populate the master calendar with dates it only generates a

      subset of the years. Is this because I'm overflowing some datatype?

       

      If I use the year function directly on the data in my table I can succesfully generate

      a list of years, just not autogenerate a calendar based on min and max.

       

      Any ideas?

       

      Many Thanks

      Harry    

        • Re: Large year ranges
          Stefan Wühl

          Harry,

           

          I don't think it's a matter of overflowing data types. You could easily check this if you set varMinDate and varMaxDate manually to some appropriate numbers.

           

          I see two issues here:

           

          1)  Your raw Date are values with nine digits and look like 183104242, and you use

          date#(mid(Date,1,8),'YYYYMMDD')

          to parse your values into Date type. That works just fine with values like 183104242 (April 24, 1831), but you also have values like  177500002 or even just 0. Both will not be succesfully parsed, because they miss an appropriate format (177500002 would imply Month 0, Day 0, which is invalid, same for just 0).

          So you need to decide what to do with these values, how to detect and handle them.

           

          2) You use the peek function to retrieve the first and last CalendarDate record from your Dates table. But the table is unsorted, so you won't necessarily get the min and max CalendarDates in your varMinDate and varMaxDate. Because of 1), some rows won't have a valid CalendarDate, and in your sample the last record shows a NULL. This is why your master calendar won't be built correctly.

          If you fix 1) and sort your Dates table by Date, the master table should just work fine.

           

          Hope this helps,

          Stefan

           

          edit: One more note: You might encounter some issues in displaying CalendarDates with dates b.c. though.

          Here it seems that the date formatting  using date() will not display the values correctly.