Chris Thomas
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm guessing that it's subtracting 1 quarter from the date? So this will output the date parameter minus 3 months?
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
q - quarter
m - month
w-week
y- year
there are many others
ASKER
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
What database are you using?
mlmcc
ASKER
SQL server, 2012 I believe
ASKER
Getting 'val' is not a recognized built in function
Looks like I might need to use CONVERT?
Looks like I might need to use CONVERT?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent, thanks!!!
I changed it to m, -3 just to make sure it sticks to using months
I changed it to m, -3 just to make sure it sticks to using months
ASKER