Crystal - manipulating date field when it is a string

Chris Thomas
Chris Thomas used Ask the Experts™
on
I have a report that I'd like to have a second version of that runs by 3 months instead of 12. The report will prompt for the ending date, and will currently run based off of a range of 12 months prior. The prompt accepts the date as YYYYMM, ie 201801. It is a string variable, which makes calculations against it (like subtracting a year for the range) difficult. I could add in a starting date field, but I like for the report to figure everything out with minimal input. Getting a range for year is not so hard, just subtracting 100 from the entered value. Switching to a month range is more difficult though. I would need to subtract 2 from the input date, but not if it ends in 1 or 2. I would then need to subtract a year and a month or 2. I'm not sure how to do this though.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Try this formula

CStr(DateAdd('q',-1,Date(val(left({?DateParameter},4)),val(right({?DateParameter},2)),1)), 'yyyyMM')

You can change the 'q' to 'm' for months

mlmcc

Author

Commented:
What does the q do?

Author

Commented:
I'm guessing that it's subtracting 1 quarter from the date? So this will output the date parameter minus 3 months?
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
DateAdd allows you to add(subtract) any type of period to/from a date.

q - quarter
m - month
w-week
y- year

there are many others

Author

Commented:
Does this in in an SQL command? I just realized that this report is built in a command
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You can use it in a command.  The actual functions called may have to change to use database functions.

What database are you using?

mlmcc

Author

Commented:
SQL server, 2012 I believe

Author

Commented:
Getting 'val' is not a recognized built in function

Looks like I might need to use CONVERT?
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Try using this

Left(Convert(varchar, DateAdd(q, -1, Convert(datetime, Concat(Concat(Concat(left({?DateParameter}, 4) , '/' ), right({?DateParameter}, 2)), '/01'))), 112), 6)

Open in new window



mlmcc

Author

Commented:
Excellent, thanks!!!

I changed it to m, -3 just to make sure it sticks to using months

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial