Date conversions

[DJWheezyWeez]

I'm using CR XI.

If I have a date of 3/1/2009, is there a way in a formula to make it read March 2009? I know I can manually change the format of it to do what I want but I was hoping for a way to do it in a formula. Is this possible?

-DJWW

 

[Turkbear]

Hi,
A formula like:

MonthName(Month({TableDateField}) + " " +  Year({TableDateField})

Should do that..place in in the report instead of the actual field.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[DJWheezyWeez]

I'm getting the error "A number, currency amount, date, time, or date-time is required here" highlighting the " ".

 

[Turkbear]

Hi,
Sorry, I forgot that the YEAR function returns a number, so try:

MonthName(Month({TableDateField}) + " " +  ToText(Year({TableDateField}),0)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[BobSuruncle]

Try this:

MonthName(Month({TableDateField}) & " " &  ToText(Year({TableDateField}),0,'')

Bob Suruncle

 

[DJWheezyWeez]

Turkbear, I'm getting the same error in the same place.
Bob, I'm getting the error "A number is required here" for everything inside MonthName().

Maybe I'm doing something wrong. I'm making a new formula, @Date, and using, for Bob's example, MonthName(Month({'Sales_Journal_'.Date}) & " " & ToText(Year({'Sales_Journal_'.Date}),0,'')).

Am I wrong or does the formula just need some tweeking or something?

 

[BobSuruncle]

Is your date field REALLY a date field?
If you place it on the report canvas and format it, is there a Date tab on the format editor?
If you turn on "Show Field Type" in the Field Explorer, what type does it show?
My guess is that it's a Date-time string, in which case you'll need to extract the parts you need.
For example, if the string is in the following format:
yyyy-MM-dd hh:mm:ss
then you'd need to pull out the year and month portions, and then use the formula in the way mentioned earlier.
e.g.

Local StringVar dt := {'Sales_Journal_'.Date} ;
Local StringVar yr := Left(dt,4) ;
Local NumberVar mth := ToNumber( mid(dt,6,2) ) ;
MonthName(mth) & " " & yr

Bob Suruncle

 

[Turkbear]

Hi,
I agree, it looks like a field being used as a date but not DATE type - What database are you connecting to?

Try:

MonthName(Month(Date({TableDateField})) + " " +  ToText(Year(Date({TableDateField})),0)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[DJWheezyWeez]

Bob, It does show yyyy-mm-dd hh:mm:ss but your code doesn't work. It says a string is needed for {'Sales_Journal_'.Date}. I changed it to:
Local datetimeVar dt := {'Sales_Journal_'.Date} ;
Local datetimeVar yr := Left(dt,4) ;
but after getting that far, it says I need a string for dt in Left(dt,4) ;

I'm pretty sure all that's wrong with your code is type conversions but I've played around with it the best I know how and ended up going in a circle.

Turkbear, still the same error.

I'm connecting to an excel spreadsheet I made. The data in the spreadsheet is in a custom date format but it's still the normal mm/dd/yyyy format. When I bring it into CR, it turns it into a datetime formatted field.

 

[DJWheezyWeez]

Ok I've done some sleuthing and I think I know a way to fix it just not how. I know it's messy but I use:

Local datetimeVar dt := {'Sales_Journal_'.Date};
Local stringVar yr := mid(cstr(dt),5,4);
Local numberVar mth := tonumber(mid(cstr(dt),1,2));
MonthName(mth) & " " & yr

That gives me a year of 2009 but a month of 0. The actual month it's giving me is 4/ and tells me it's non-numeric because the 2nd character is a slash.

I think if there's a way to make a date of 4/1/2009 into 04/01/2009, then I can make this all work out. Is there a way to do this?

 

[BobSuruncle]

If this field is really a date (and it SEEMS like it is), then try this:

ToText({'Sales_Journal_'.Date},"MMMM yyyy")

Bob Suruncle

 

[DJWheezyWeez]

That was surprizingly simple. Only problem is now is that it's a string and will be sorted as a string alphabetically instead of like a date. I was also hoping to be able to use this as a date parameter. I guess what I'm overall trying to do is have a field that I can use as a date parameter what I'll be able to only type in the month and year and then show that field in the report too. Does this make any sense? I have a feeling this will be somewhat complicated...

 

[Turkbear]

Hi,
You can sort on the actual field even though it is not in the report.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[BobSuruncle]

You can still sort on the original field instead of this formula.
Or, if you're using it to group, then use a different formula that WILL sort correctly, and then use the original formula as the custom name for the group.

e.g. a formula that will return the date sorted correctly
@DateSort

ToText({'Sales_Journal_'.Date},"yyyy-MM-dd")

Group on this formula, but in the Custom Name for the group, use the original formula.


Bob Suruncle

 

[DJWheezyWeez]

I'm not sorting or grouping by this date. I'm only using it as a parameter. I want to make a date parameter so when I run the report, I fill in January 2009 or 1/2009 or use a drop down, something like that. By itself that could work but when I want a date range and have beginning and end dates, if I run it for January 2009 through June 2009, I'm going to get only those two months because it's alphabetical. I need some way to have them ordered by date so if I do a range, I get the date range and not the alphabetical range.

 

[DJWheezyWeez]

Got it to work a different way. No need to continue this thread but thanks for the help Turkbear and Bob.