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

[Tom Whiteside]

Hi, bls!

Try this thread (http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000009#000000) 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:

Txt_Date=Dtoc([Orig_Date])

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

Month=Ctod(Substr([Txt_Date],1,3)+”01”+Substr([Txt_Date],6,3))

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.Now, let us know if have anything else to use your date for - - the possibilities are endless!

Best of luck!

[ May 01, 2006, 05:39 PM: Message edited by: Todd Niemi ]

[helpless]

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

[Grant Perkins]

Just revisited this as it came to the top of the list and I suddenly remembered Winn's solution to the 'back one month problem' (http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000197#000009) 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.

Grant

[ May 01, 2006, 05:40 PM: Message edited by: Todd Niemi ]