4 Replies Latest reply: May 15, 2014 9:53 AM by Gareth Horton RSS

    Date format limitations

    Viv _

      In the help file it says 'You can assign date/time type to any field whose values represent valid dates in the range January 01, 1900 - December 31, 2099.'

       

      I work in the property industry and we often need to use dates greater than this. I cannot see any mathematical reason for this limitation and I can only assume that the date chosen is an arbitrary value.

       

      Is this limitation going to be addressed in the future? Any ideas for a work around? I need to return these dates, but, they are evaluated as 'null'.

        • Date format limitations
          Grant Perkins

          Viv,

           

          I note from the help sections that several other tools (Excel for example) have limitations in the area of dates as well. I assume you have some special software for calculation purposes or output the dates as text strings but I would love to know the real answer.

           

          Other than suggesting going down the "store as text" route and dealing with calculations on a case by case basis, there does not seem to be much that will address the issue, though I note one slightly obscure section of the Help (found with 'date' as the search and referenced as a solo word in the results window) that dates from 1601 to 2400 can be used if a 4 digit year is provided.

           

          Copied below:

           

          "

          date

           

          Dates must be in the range 1900 - 2098.  When a date constant is entered in an expression, it must be enclosed in braces, as in HIRE_DATE=.

           

          Years 1601 to 2400 (when year is expressed as YYYY), 1900 to 2098 (when expressed as 2 digits)."

           

          Unfortunately I have not been able to quickly identify the context of the message.

           

          Hope this is of interest.

           

          Grant

           

           

            Originally posted by Viv:

          In the help file it says 'You can assign date/time type to any field whose values represent valid dates in the range January 01, 1900 - December 31, 2099.'

           

          I work in the property industry and we often need to use dates greater than this. I cannot see any mathematical reason for this limitation and I can only assume that the date chosen is an arbitrary value.

           

          Is this limitation going to be addressed in the future? Any ideas for a work around? I need to return these dates, but, they are evaluated as 'null'. /b[/quote]

           

          [size="1"][ October 29, 2004, 08:46 AM: Message edited by: Grant Perkins ][/size]

          • Date format limitations
            Viv _

            I am not quite clear about your reply ? Perhaps it will help if I elaborate about what we are doing here. The data is downloaded as text and the dates are downloaded as serial numbers. Using the Monarch model, we convert the serial numbers back into dates. However, any dates that exceed December 31, 2099 are returned as nulls. The Monarch help file confirms that these dates are not supported by Monarch. My question is whether this is likely to change in the future and if not, have you any idea how we can convert the problem date values ?

            • Date format limitations
              Grant Perkins

              Viv,

               

              Sorry, re-reading my earlier post suggests I should not post so late at night! I have edited to make it a little easier to read (I hope).

               

              I can't answer the "will it be addressed in the future question" (that is one for Datawatch) and I don't have a ready answer for a work around (if there is one).

               

              However I am interested to know if you have other software you use regularly that deals with the issue as it would appear that Excel and other common applications do not. I guess it is really a question of whether the conversion of the serial number to a date is the end of the requirement or whether the result is then passed to another system for further processing. If the latter any work around might need to take that into account.

               

              That said, if I try to read the dates from a 'report' file Monarch (I am trying this with V7.02 Pro) interprets 01/01/1601 and 31/12/2400 correctly as dates, anything outside the range appearing as blank if the fields are set to be date format fields.

               

              Presumably if you need to export those higher dates to Excel you would need to export as Monarch V3  (Excel 2.1) format.

               

              I must be honest an say that this is an interesting point to understand (and file away in memory) for me but not an everyday requirement.

               

               

              Grant

               

               

                Originally posted by Viv:

              I am not quite clear about your reply ? Perhaps it will help if I elaborate about what we are doing here. The data is downloaded as text and the dates are downloaded as serial numbers. Using the Monarch model, we convert the serial numbers back into dates. However, any dates that exceed December 31, 2099 are returned as nulls. The Monarch help file confirms that these dates are not supported by Monarch. My question is whether this is likely to change in the future and if not, have you any idea how we can convert the problem date values ? /b[/quote]

               

              [size="1"][ October 29, 2004, 09:20 AM: Message edited by: Grant Perkins ][/size]

              • Date format limitations
                Gareth Horton

                Viv

                 

                As I understand it, you are converting the serial to a date in some way, so the date is actually a calculated field, not a field extracted from the text via a template.

                 

                I am using V7.01 and have no problems with the following calculated field, for example:

                 

                Ctod("01/20/2300","m/d/y",)

                 

                This gives a valid date of 20/01/2300

                 

                Is there something else going on here?

                 

                 

                Gareth        

                 

                 

                Originally posted by Viv:

                I am not quite clear about your reply ? Perhaps it will help if I elaborate about what we are doing here. The data is downloaded as text and the dates are downloaded as serial numbers. Using the Monarch model, we convert the serial numbers back into dates. However, any dates that exceed December 31, 2099 are returned as nulls. The Monarch help file confirms that these dates are not supported by Monarch. My question is whether this is likely to change in the future and if not, have you any idea how we can convert the problem date values ? /b[/quote]