4 Replies Latest reply: May 15, 2014 9:58 AM by ron cousino RSS

    Numerics in database for dates

    ron cousino

      OUr database does not store dates as dates but as numerics.  So 11/01/2005 is a numeric 20051101.  This creates  opportunities when one wants to summarize data by year and month over a range of years.  It would be nice to have a function to convert a number to a date.  In any case, does anyone have a suggestion for splitting the numeric that might be shorter than the calculated fields I am creating to get the year and month?

        • Numerics in database for dates
          Grant Perkins

          Hi Ron,


          I cannot recall which version of Monarch you have and I don't know whether this suggestion is any shorter than the formula you use now but here goes. (I'm not claiming this is state of the art but it is fairly easy to apply.)


          In V7 and V8 there are some functions to make life easier to extract a year or month (or day) from a date - Year() and Month() for example. There are also routines to convert a string (character) to a date  - CtoD and date to Character, DtoC.


          So we need to create a calculated field which makes use of these functions.


          You can convert your numeric input to a character value field using the STR() function. If your input field is called F1 for example

          [font="courier"]str(f1) /font[/quote]will provide a character version of it.



          [font="courier"]ctod(str(F1),"y/m/d","D")[/font][/quote]will allow the characters to be presented as a date field. We are telling ctod that the incoming string is to be read as year-month-day and we just want the date and not any time information that may be contained in the string.


          [font="courier"]year(ctod(str(F1),"y/m/d","D"))[/font][/quote]would give a numeric field with the year number from the date.


          [font="courier"]month(ctod(str(F1),"y/m/d","D"))[/font][/quote]would give a numeric field with the month number from the date.


          [font="courier"]str(year(ctod(str(F1),"y/m/d","D")))"/"month(ctod(str(F1),"y/m/d","D"))[/font][/quote]Would give year/month as a text string - e.g. 2005/11


          If you want month as NOV rather than 11 you could use a lookup table and substitute the result of the lookup for the month number.


          On the other hand, assuming that your date field in the database is always a consistent 8 characters, you could simply use str to make it a character strings and then slice and dice the values directly using SUBSTR.


          So SUBSTR(str(f1),1,4) would give you the year and SUBSTR(str(f1),5,2) would give the month. Both as CHARACTER fields. Use VAL to convert them back to numeric if you need to for any reason.


          And if you have V8 don't forget to check out the new AGE function to see if that has any benefits to offer you.


          Are these suggestions any better than you use currently?




          [size="1"][ November 17, 2005, 11:58 AM: Message edited by: Grant Perkins ][/size]

          • Numerics in database for dates
            ron cousino

            Grant, thanks I will try these out. I think they will work well.  I am using Monarch Pro V8.1.  I have another need.  When I query our database of demographic data which includes a SS# Monarch Pro interprets the field as a character.  Wouldn't be a problem except it truncates leading zeroes.  So for a Social Security # of 038492879 Monarch only returns 38492879.  Any suggestion on how to remedy?

            • Numerics in database for dates
              Grant Perkins

              Originally posted by ron cousino:

                 So for a Social Security # of 038492879 Monarch only returns 38492879.  Any suggestion on how to remedy? /b[/quote]If the database stores the number as a character field I would expect that you could import it as a character field in which case it should retain the leading zero(s). If you are getting a different result that is a little strange.


              On export you can specify that you want to fill the field with leading (or trailing if left justified) zeros but that won't help you while the field is in Monarch. There is little point in have a reference number field as a numeric - I can't think of a good reason for performing calculations on a reference number as a matter of regular activity. (I guess you will now all bombard me with perfectly acceptable examples ...!   The only one I can think of is that it might be useful if the original program that created the number was doing so based on issuing a sequence of numbers over time.          ).


              There are ways of fooling the system into displaying strings with leading zeros even as numeric fields but such an approach needs to be handled carefully for the avoidance of confusion at some point. I seem to recall there have been a couple of posts on this sort of thing in the past so will have a search.


              One possiblity is to simply convert the numeric field to a calculated character field with the zero(s) tacked on at the start.







              Edit to add ...


              Several references turn up searching on 'leading zero'


              [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000361#000005"]This /url one looks quite useful.


              [size="1"][ May 02, 2006, 04:12 PM: Message edited by: Todd Niemi ][/size]

              • Numerics in database for dates
                ron cousino

                I would like to know also why Monarch Pro reads a numeric field as a character as well when it is defined as a numeric in the database.  Your examples just might solve the problem.  Thank you very much.