SSRS Convert date and groupby

Question

I have a DateTime type column called CreatedDate in my SQL table, and I have created a report using SSRS 2008.

I have grouped my table in my report with this CreatedDate.

I need to orderby this CreatedDate in the format of dd/MM/yyyy.

If I convert this column value as Convert(varchar(10),CreateDate,101) am getting the values in the format of MM/dd/yyyy and its sorting the datas correctly like,

03/03/2012
03/05/2012
05/03/2012

The same way If I convert the column as Convert(varchar(10),CreatedDate,103) am getting the values in the format of dd/MM/yyyy and its sorting like

03/03/2012
04/05/2012
05/03/2012

but I need to group the table like

03/03/2012
05/03/2012
04/05/2012

like this, for this I tried giving the sort function in the tablix manually like

=Format(Fields!CreatedDate.value,"dd/MM/yyyy")

but its not working, how can I fix this....can anyone help me here...

the query am using here is

SELECT ItemName
       , COUNT(ItemName) AS Quantity
       , SUM(LineTotal) AS Amount
       , CONVERT(varchar(10), CreatedDate, 103) AS CreatedDate
FROM StudentFeesItems
WHERE (CONVERT(varchar(10), CreatedDate, 101) BETWEEN @StartDate AND @EndDate)
GROUP BY ItemName, CreatedDate

Answer 1

You can add the date on those 2 formats on the dataset and then sort by one of them:

SELECT [YourColumns], Convert(varchar(8),CreateDate,112) SortDate,
       Convert(varchar(10),CreatedDate,103) Displaydate
FROM YourTable

This way, you can use DisplayDate on your tablix and sort manually by SortDate.

Answer 2

Don't convert your date to strings. Keep them as dates and sort them as dates.

As you've already experienced, unless you have the string formatted as YYYY-MM-DD you won't get the order that you want. If you keep the date as a DATETIME datatype, you can order by it, group by it, format it and do what you like to it.

DATEADD(DAY, DATEDIFF(DAY, 0, CreateDate), 0) AS roundedDate

RE-EDIT

Here is my suggestion in your query...

WITH
  main_query
AS
(
  SELECT
    ItemName,
    DATEADD(DAY, DATEDIFF(DAY, 0, CreatedDate), 0) AS CreatedDate,
    COUNT(ItemName) AS Quantity,
    SUM(LineTotal) AS Amount
  FROM
    StudentFeesItems
  WHERE
        CreatedDate >= @StartDate
    AND CreatedDate <  DATEADD(DAY, 1, @EndDate)
  GROUP BY
    ItemName,
    DATEADD(DAY, DATEDIFF(DAY, 0, CreatedDate), 0)
)
SELECT
  ItemName,
  Convert(varchar(10), CreatedDate, 103)    AS formattedDate,
  Quantity,
  Amount
FROM
  main_query
ORDER BY
  ItemName,
  CreatedDate

Notes:
- Don't call a function on a field and THEN filter it
- Don't use string manilpulation to round a datetime to a date
- Don't sort by the string representation of dates