SQL Expression for last 6 months

[FireGeek21]

I am using a SQL Expression to pull in some data. I need a value if it occurs in the last 6 months from today. This file will run weekly so the last 6 months is a moving date range.

In the WHERE clause, I have been able to use a date range when the start/stop is known such as the following for 1/1/2014 - 12/31/2014:

WHERE DATE >= {fn CONCAT({fn CONVERT({fn YEAR({fn CURDATE()})},SQL_VARCHAR)}, '-01-01')}
and DATE <= {fn CONCAT({fn CONVERT({fn YEAR({fn CURDATE()})},SQL_VARCHAR)}, '-12-31')}

Here is where I am trying to capture the start/stop date - the start point is the problem:

WHERE DATE >= [TODAY - 6 months or TODAY - 180 days]
and DATE <= {fn CURDATE()})

Thank you for any help you can offer!!!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[briangriffin]

You can create the filter in the Select Expert in Crystal:

where YourDateField >= dateadd("M",-6,currentdate)
and YourDateField <= currentdate

Then take a look at Show SQL Query from the Database menu in Crystal. You should see the logic in the Where clause, but if not the SQL expression in MySQL is:

adddate(curdate(),INTERVAL -6 MONTH)

Either way, the important thing is to have the filtering done by the database, not CR.

 

[FireGeek21]

Thank you Brian for the suggestion.

Unfortunately, the Show SQL Query gives me:

WHERE TABLE.DATE>={ts '2014-05-20 00:00:00'} AND TABLE.DATE<{ts '2014-11-21 00:00:00'})

It's not providing me the functions to get these dates.

AddDate is not an allowed function in the SQL Expression. We are on a SQL Windows server if that helps.

Thanks!!



FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[briangriffin]

SQL Server syntax would be:

dateadd(m,-6,cast(getdate() as date))

But what you're showing is what I would hope to see - you've created the formula in the Select Expert, then that is translated into this SQL:

WHERE TABLE.DATE>={ts '2014-05-20 00:00:00'} AND TABLE.DATE<{ts '2014-11-21 00:00:00'})

That tells me that the formula is working and is being passed to the database server. Test it by changing the -6 to -5 and you'll see that the date in the where clause also changes.

I rarely use SQL Expressions, and then only when there is a function in the database that I can't replicate with a Crystal function. It doesn't seem like you need one here, but I may be missing something.