2 Replies Latest reply on Jan 6, 2012 8:51 AM by AdamReed

    date vs day, month, year



      date vs day, month, year


      I'd like to be able to do calculations based upon dates, but I also need to have some flexibility in the dates (not all will have a specific date, but might be only "July 2014", "Summer 2014", or simply "2014".  Any general suggestions about how to handle this?



        • 1. Re: date vs day, month, year

          Dates are really integers that count the number of days elapsed from 12 / 31 / 0000 to the date stored in that field. Thus, you can use date fields in calculations much like they are number fields.

          Datefield + 1

          for example, returns the date one day later than the date stored in DateField.

          For dates where the day is unimportant, I'd use the date of the first of that month. Then format the date field to only display the month name and year, leaving the day hidden.

          You could do the same thing with year, using 1 / 1 / 2014 to indicate the year 2014. Or you can just use a number field as you have  function, Year ( dateField ) that will return the year porttion of any date. It depends on how you will use that value in the rest of your system.

          Some useful calculations:

          DateField - Day ( Datefield ) + 1

          will return the date of the first day of the month for the date in DateField. This calculation can thus be useful when you want to sort to group your records by month or establish a relationship to all records of a specific month. (Or even a range of months.)

          In similar fashion:

          DateField - Dayofweek ( datefield ) + 1

          returns the date of Sunday of the same week as teh date in datefield.

          Oh yes, and there are two ways to use constants to get a date value:

          Date ( Month ; day ; year )


          GetAsDate ( "mm/DD/YYYY" )

          • 2. Re: date vs day, month, year

            Thanks, I think that GetAsDate function will do the trick.