Date formulas

[Luvsql]

I need to create the following formulas, always based on current date and compare it to the date in the table:

Assuming today June 25, 2004

Current Month (June, 2004), Previous Month (May 2004) and P-2 Month (April 2004), Last 12 Months (ie July 2003-June 2004). I have CUrrent Month (using Monthtodate), Previous Month (LastMonthtodate), but I can't figure out the P-2 one or the last 12 months.

I'm also trying to use in a formula whether a date field is less than June 1, 2004 (which would change depending on what month I happened to be in). I was trying Month(field)-01-year(field) to create "06-01-2004" but it's creating a number.

Any thoughts?

 

[bdreed35]

@P-2 Month
IF {table.date} in dateadd("m",-1,minimum(lastfullmonth)) to minimum(lastfullmonth)-1 then
1
else
0

@Last 12 Months
if {table.date} in dateadd("yyyy",-1,dateadd("m",1,(currentdate-day(currentdate)+1))) to dateadd("m",1,(currentdate-day(currentdate)+1))-1 then
1
else
0

To create a date field you need to use the Date() or Cdate().

Cdate(year({table.date}),month({table.field}),1)



~Brian