10 Replies Latest reply: Sep 24, 2010 1:24 PM by bryankoch RSS

    Dates in Text Objects

    bryankoch

      Hi,

      I've been reading around and haven't been able to find any information on this, so i'll give this a shot.

      I would like to create a label via the Text object that displays something along the lines of

       

      'Information from (start date) to (end date) for so and so'

      I've put this:

      date#(@10,'MM/DD/YYYY') as [Date of Service], Month(@10) as Month, Year(@10) as Year,
      date(DayStart(@10), 'MM/DD/YYYY') as SDay,
      date(DayEnd(@10), 'MM/DD/YYYY') as EDay,

      in my script, but when I enter this

      ='Data dates from ' & [SDay] & 'to ' [EDay]

      into the expression box, I get a null value.

       

      Any idea where I'm going wrong?

       

        • Dates in Text Objects
          Karl Pover

          You're missing a & between to and EDay.

          ='Data dates from ' & [SDay] & 'to ' & [EDay]

          You might also need to put min(SDay) and max(EDay).

          Regards.

           

            • Dates in Text Objects
              bryankoch

              ='Data dates from ' & min( [SDay]) & 'to ' &max( [EDay])

               

              yields

              "Data dates from to "

              It doesn't bother displaying the dates.

                • Dates in Text Objects
                  Karl Pover

                  If you make a list of SDay, what values do you get?

                  Also, are the values aligned to the left or the right? If they are aligned left they are text and if they are aligned right they are numbers. You need them to be numbers.

                  Regards.

                    • Dates in Text Objects
                      bryankoch

                      Well it appears that there are no values in the text box either. Date of Service is importing in as text, but it is showing.

                       

                      I guess there's a conversion method to switch it from text to numeric then convert it back into a date?

                        • Dates in Text Objects
                          Karl Pover

                          In you script, try the following:

                          date(date#(@10,'MM/DD/YYYY')) as [Date of Service],
                          Month(date(date#(@10,'MM/DD/YYYY'))) as Month,
                          Year(date(date#(@10,'MM/DD/YYYY'))) as Year,
                          DayStart(date(date#(@10,'MM/DD/YYYY'))) as SDay,
                          DayEnd(date(date#(@10,'MM/DD/YYYY'))) as EDay,

                          A load of a load might be even better so that you don't have to repeat date(date#(@10,'MM/DD/YYYY')) so many times.

                          Regards.

                            • Dates in Text Objects
                              bryankoch

                              Still nothing. :-/ The list box doesn't show anything for SDay.

                              I'm wondering at this point if it's the format the dates are in when imported. They come in as:

                               

                              05-OCT-09

                              DD-MM-YY

                                • Dates in Text Objects
                                  Karl Pover

                                  Try this then

                                  date(date#(@10,'DD-MMM-YY')) as [Date of Service],
                                  Month(date(date#(@10,'DD-MMM-YY'))) as Month,
                                  Year(date(date#(@10,'DD-MMM-YY'))) as Year,
                                  DayStart(date(date#(@10,'DD-MMM-YY'))) as SDay,
                                  DayEnd(date(date#(@10,'DD-MMM-YY'))) as EDay,

                                    • Dates in Text Objects
                                      bryankoch

                                      Ah progress.

                                      That shows the date, but it's got an ugly time stamp next to it now.

                                      "Data dates from 12/7/2009 12:00:00 AM to 2/10/2010 11:59:59 PM"

                                      So I changed my expression to:

                                      ='Data dates from ' & date(min( [SDay]), 'MM/DD/YYYY') & 'to ' & date(max( [EDay]), 'MM/DD/YYYY')

                                      and I have

                                      "Data dates from 12/07/2009 to 02/10/2010"

                                       

                                      Just to verify, in the script it's specifying the format it's in and not as I'd like it?