Link to home
Start Free TrialLog in
Avatar of Chris Thomas
Chris ThomasFlag for United States of America

asked on

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.
SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Chris Thomas

ASKER

What does the q do?
I'm guessing that it's subtracting 1 quarter from the date? So this will output the date parameter minus 3 months?
Avatar of Mike McCracken
Mike McCracken

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

Looks like I might need to use CONVERT?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent, thanks!!!

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