Extracting MM from CCYYMMDD format 2

[TheMagikWand]

hey guys/gals,

i have a number field that contains entry dates for specific procedures, it is listed in CCYYMMDD format (so i can use the numbertodate(x) function).

what is need to know is how do i isolate just the month part of the field, so that i can group the entrys by month they were produced

also will i have a problem w/ entryless dates, (exe. all most saturdays and sundays)

thanx in advance,
B U D

 

[vidru]

This formula will extract the MM part:

numberVar x := int({EntryDateFieldHere}/100);
x := x mod 100;
x;

Need a little more information about the entryless dates. Does that mean that there will be some records that don't have the CCYYMMDD format? Will they be NULL, or set to some other number?

-dave

 

[synapsevampire]

As Dave alluded to, a null may be a problem, but I'm not sure how numbertodate() deals with nulls, it probably returns a blank, but just in case:

if isnull({table.field}) or {table.field} = 0 then
0
else
month(numbertodate({table.field}))

Remember that grouping by month will not allow for the year, so you might be better served grouping by year and month (note I convert to text here):

if isnull({table.field}) or {table.field} = 0 then
"0"
else
totext(numbertodate({table.field})),"yyyyMM&quot

-k

 

[TheMagikWand]

wow thanx alot guys that formula works perfectly

and as for the entryless-dates i meant that we have blank spaces for most sats and sundays, because were mainly a mon-fri busniess. but either way, problem solved

thanx again,
B U D

 

[crystalreporting]

NumberToDate returns 11/30/-2 for null dates - go figure. Couldn't you just create a formula to return the date, and then choose 'for each month' when you group by the formula? This would handle months/years too... just a thought.

Peter Shirley

http://www.linkcorp.com

 

[dgillz]

peter,

What happens to null dates when you activate "convert null field values to default" under file options?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com