Crystal - manipulating date field when it is a string

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.
Chris ThomasISSAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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
0
Chris ThomasISSAuthor Commented:
What does the q do?
0
Chris ThomasISSAuthor Commented:
I'm guessing that it's subtracting 1 quarter from the date? So this will output the date parameter minus 3 months?
0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

mlmccCommented:
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
0
Chris ThomasISSAuthor Commented:
Does this in in an SQL command? I just realized that this report is built in a command
0
mlmccCommented:
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
0
Chris ThomasISSAuthor Commented:
SQL server, 2012 I believe
0
Chris ThomasISSAuthor Commented:
Getting 'val' is not a recognized built in function

Looks like I might need to use CONVERT?
0
mlmccCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris ThomasISSAuthor Commented:
Excellent, thanks!!!

I changed it to m, -3 just to make sure it sticks to using months
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.