Geek asks and answers

SSRS Convert Date and Group

I have a column of type DateTime with a name CreatedDatein my SQL table, and I created a report using SSRS 2008.

I grouped my table in my report using this CreatedDate.

I need to arrange this CreatedDatein a format dd/MM/yyyy.

If I convert this column value as Convert(varchar(10),CreateDate,101)am, getting the values ​​in format MM/dd/yyyyand sorting them correctly,

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

Similarly, if I convert the column as Convert(varchar(10),CreatedDate,103)am, I get the values ​​in the format dd/MM/yyyyand sort it as

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 to give the sort function to tablix manually, for example

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

but its not working, how can I fix it .... can someone help me here ...

query used here

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
+3
3

2 , :

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

, DisplayDate tablix SortDate.

+2

. .

, , YYYY-MM-DD, . DATETIME, , , , .

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


RE-EDIT

...

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

:
- , THEN -
-

+2

how about adding ROW_NUMBER() over (order by CreatedDate) as myOrderto your SQL query and order by myOrder?

EDIT:

=Format(Fields!date.Value,"dd/MM/yyyy")
+2
source

More articles:


All Articles