Kazmerelda (9/26/2016)
Sorry foe the delay, I was out last Monday on holiday so couldn't test it properly.So I went in to test this morning all hopeful....and sadly I am getting an error because the date format I have chosen for the field is dd/mm/yyyy where I have used convert (varchar(10), date, 103). So using dateadd or weekday with that doesn't work (numbnuts here didn't think of that!).
So my next question is how would I convert the format in SSRS, as if I change the date format in SQL it works (as I expected but tested anyway). However I need it in a dd/mm/yyyy format.
You are going to tell me this is an easy one aren't you? 😛
Right click the cell with the date > Text Properties > Number > Date > [Select your desired format]
If you need to do date calculations in an expression, you may need to use the "cDate" (convert to date) function on the field if it is not coming out of the query as a date. Be careful here as you will need to handle nulls and empty strings among possibly a few other things.
----------------------------------------------------