Thank you Thom for this explanation.

You have been a great help.

Paul.

Thank you all for your replies.

I know what is happening now

If I filter on datetime like so....

select orderdate,orderdatestring from QuartzReportnew where...

Yes, sorry...ORDERDATE not ORDERDATETIME.

Not sure how to post sample data....

These are the 2 queries I run in SQL on the views

Hi Thom, 

the error I get is

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
----------------------------
Cannot read the...

I see, you mean the performance may be compromised.

There is not a whole lot of data in the database and it works fine as it is. i'll leave...

The whole second script you sent. I am unsure what is happening in that.

I have altered the view to contain Year_Month with this line

...

I am confused now.

It's ok, I got it working now. I used your code to get the yearmonth (Year_Month = CONVERT(CHAR(6),ORDERDATEANDTIME,112), instead of concatenating the year and month 

Thanks again Chris. I...

Your's works fine, I think the problem with mine is when I am getting year month it's not putting a zero between months 1 to 9.

For example March...

Thank you for your help Chris.

It's nearly there but it's still giving me months I didn't ask for.

If I choose October 2016 to March 2017...

I do have the month and year as parameters. I have start month, end month, start year and end year

Thank you all for the replies. The view I was creating was to be accessed via SSRS. Now I know SSRS can take care of the totals for the month...