3 Replies Latest reply: May 15, 2014 9:59 AM by Grant Perkins RSS

    Date work

    helpless _

      I have a date field on a text report that I split into two fields

      Begmonth & Endmonth.


      So the date in the report goes from 1/12/02 to

      "1"   "02".


      I was hoping there was someway to put the date back, always have the first of the month (i.e. 1/12/02 to 1/1/02). I hope this makes sense!! Does anyone know how to do this?

      Thanks in advance

        • Date work
          Tom Whiteside

          Hi, bls!


          Try this [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000009#000000"]thread[/url] from last year.  If there's too much to plod through - - after all, it's one of mine - - just go for the relevant part of the topic, namely:


            If your report starts out with a “real” date, e.g., 07/14/02, and is called, e.g., Orig_Date, then use the date-to-character function to turn it into a text date:




          Because of the mm/dd/yy format, use a modification of the dd/mmm/yy formula used in my original tip:




          This should give you the “real” date of 07/01/02.  The messy substring formula takes any text date of "mm/dd/yy" and changes it to a text date of "mm/01/yy," then Ctod changes it to a “real” date of mm/01/yy.[/quote]Now, let us know if have anything else to use your date for - - the possibilities are endless!


          Best of luck!


          [size="1"][ May 01, 2006, 05:39 PM: Message edited by: Todd Niemi ][/size]

          • Date work
            helpless _

            Worked like a charm! I can also apply this to other templates!!!


            • Date work
              Grant Perkins

              Just revisited this as it came to the top of the list and I suddenly remembered Winn's solution to the  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000197#000009"]'back one month problem'[/url] from May this year.


              That was a little more complex than this requirement as it was a month shift and the number of days in a month became significant.


              Here it would be a bit easier. Haven't tried it but it seems to me that it should be possible to get the date day ( say the 7th of the month) and simply subtract the (number of days -1) that gives from the reported date, thus giving the 1st of the month. Something similar could be done with the month calculation.


              Any comments? Looks like 2 solutions for the price of one but I may have missed something.




              [size="1"][ May 01, 2006, 05:40 PM: Message edited by: Todd Niemi ][/size]