first day of month in command

[lauriesamh]

I'm using CR10 and I have a command based report.
I need to change part of the command that reads

WHERE ELIG.EndDate > {?End Date}AND ELIG.BeginDate<={?Begin Date}

To find the first day of the month based on the ELIG.EndDate.

I tried using the formula in the command but it doesn't work.

Where ELIG.EndDate > {?End Date}AND
dateserial(year({ELIG.EndDate}),month({ELIG.EndDate})-1,1)<={?Begin Date}

I'm hoping it is an easy way to do this.

 

[lbass]

What functions are available depends upon your database--what type is it?

-LB

 

[lauriesamh]

It is a Windows 2000 server I believe

 

[lbass]

No, I mean like Oracle, SQL Server, Excel, Access, etc.

Try opening a new report with the same datasource, and then go to the field explorer->SQL expressions and check what functions are available in the functions list. You should be able to use these in your command.

-LB

 

[lauriesamh]

I see them but the functions are very sparse. I've got Difference(); basic date functions such as DAYOFMONTH() and only basic Arithmatic options

 

[lbass]

Try using:

table.date-{fn dayofmonth(table.date)}+1

-LB

 

[lauriesamh]

works great! Thanks lbass!!!