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?
 
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
 
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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
 
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
 
Chris ThomasISSAuthor Commented:
Excellent, thanks!!!

I changed it to m, -3 just to make sure it sticks to using months
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.